Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Need help with replacing NOT IN + ORDER BY with MINUS

Need help with replacing NOT IN + ORDER BY with MINUS

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Tue, 19 Aug 2003 22:09:45 GMT
Message-ID: <3f429d4a.115098813@nyc.news.speakeasy.net>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US