Naveen,

The column does have nulls and this was the problem. Thanks for the = insight.

Thanks

Siddharth Haldankar

Zensar Technologies Ltd.

Cisco Systems Inc.

(Offshore Development Center)

# : 091 020 4128394

shaldank@cisco.com

s.haldankar@zensar.com =

-----Original = Message-----
From: = ml-errors@fatcity.com [mailto:ml-errors@fatcity.com] On = Behalf Of Naveen, Nahata (IE10)
Sent: Thursday, December = 18, 2003 11:59 AM
To: Multiple recipients = of list ORACLE-L
Subject: RE: Any known = problems using NOT IN ?

Doess the column crs.os_id_fk1 contain nulls? =

Regards

Naveen

-----Original Message-----
From: Siddharth Haldankar [mailto:shaldank@cisco.com]
Sent: Thursday, December = 18, 2003 10:54 AM
To: Multiple recipients = of list ORACLE-L
Subject: Any known = problems using NOT IN ?

Hi Gurus,

I have a problem using NOT IN clause in Oracle. However using NOT EXISTS, = gives me the right output. Are there any known limitations.

This query selects from the master records wherein child records are not = active.

select * from ct_software_release csr where

csr.class         =3D = 'NS'

AND    csr.active_flag   =3D = 'Y'

AND    csr.os_id_pk not IN

(SELECT crs.os_id_fk1 FROM CT_ROADMAP_SOFTWARE crs

        WHERE crs.active_flag =3D 'Y');

The sub-query in the above case gives 1800 rows. The above query fails to = give any rows.

           = ;            =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;

select * from ct_software_release csr where

csr.class         =3D = 'NS'

AND    csr.active_flag   =3D = 'Y'

AND    NOT EXISTS

        (SELECT 1 FROM CT_ROADMAP_SOFTWARE crs

        WHERE crs.os_id_fk1 =3D csr.os_id_pk

           = ;            =          AND crs.active_flag =3D = 'Y');           &n= bsp;           &nb= sp;           &nbs= p;            = ;

This above query works fine.

Thanks

Siddharth Haldankar

Zensar Technologies Ltd.

Cisco Systems Inc.

(Offshore Development Center)

# : 091 020 4128394

shaldank@cisco.com
s.haldankar@zensar.com =