Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Need help with replacing NOT IN + ORDER BY with MINUS
I have a query
SELECT a.col1
from A, B
where A.col1=B.col1
AND B.col2=some_var
AND A.col1 NOT IN (select C.col1 from exclude_table C where
c.col2=some_other_var)
ORDER BY
A.col3
I want to replace it with something like
SELECT a.col1
from A, B
where A.col1=B.col1
AND B.col2=some_var
MINUS
select C.col1 from exclude_table C where c.col2=some_other_var
But I lose the ORDER BY, which is unacceptable (if I add ORDER BY I get ORA-00907)
Is there are any way to do something like
SELECT a.col1, A.col3
from A, B
where A.col1=B.col1
AND B.col2=some_var
MINUS_only_on_first_column
select C.col1 from exclude_table C where c.col2=some_other_var
I don't want to join C with A in order to get A.col3 in the query below the MINUS
Thanks.
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes
remove NSPAM to email
Received on Tue Aug 19 2003 - 17:09:45 CDT