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

Don't know how to do what you want using MINUS, but have you tried this:

select a.col1
from a, b, c
where a.col1 = c.col1 (+)
and a.col1 = b.col1
and b.col2 = some_var
and (c.col1 is null or c.col2 != some_other_var) order by a.col3;

• Dave

NetComrade wrote:
> 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 - 19:31:14 CDT