Re: SQL questions???

Hi Elton

Your example select won't work: at least you have to select 1 column from the nested select, not *.
I guess what you want is the records from table1 where code1 is either '1' or '2' or '3' while code2 is either 'A' or 'B' or 'C'. One way to solve that is this:

select * from table1
where decode(code1,'1',decode(code2,'A','NOT OK','B','NOT OK','C','NOT OK','OK'),

                                 '2',decode(code2,'A','NOT OK','B','NOT
OK','C','NOT OK','OK'),
                                 '3',decode(code2,'A','NOT OK','B','NOT
OK','C','NOT OK','OK'),
                                 'OK') = 'OK';

The construction with NOT IN, I would only use if the tables where different like:
select * from table1
where code1 not in (select col1

                                from table2
                                where.....);

The same result you could get with the operator 'exists': select *
from table1 t1
where not exists (select null

                            from table2 t2
                            where t2.col1 = t1.code1
                            and ....);

Hope this helps.

Regards,
Michael Ringbo, DDE A/S

Elton Chan wrote:

> Hi all,
>
> Anybody know how to replace 'NOT IN' operator by other operations?
>
> SELECT * FROM TABLE1 WHERE
> CODE1 NOT IN (
> SELECT * FROM TABLE1 WHERE
> ( CODE1 = '1' OR CODE1 = '2' OR CODE1 = '3' )
> AND
> ( CODE2='A' OR CODE2='B' OR CODE2='C')
> );
>
> ANY clue??? I've tried to use outer join, but it cannot be used with OR
> operation...
>
> Thanks,
> Elton

--
to reply, remove the  "-nospam" from the address