Re: Need help with replacing NOT IN + ORDER BY with MINUS

andreyNSPAM_at_bookexchange.net (NetComrade) wrote in message news:<3f429d4a.115098813_at_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

Net, I am not at work right now so I cannot test this but why not just enclose the existing query in a select with an order by?

select * from (your query) order by 1;

HTH -- Mark D Powell -- Received on Tue Aug 19 2003 - 20:36:00 CDT