NOT IN QUERY

[Prabhu_7]

Hi,

will below 2 queries gives same result ?

Select distinct pctr_num from TACCT_DAILY_DEPOSIT where pctr_num not in (select distinct pctr_num from ALL_PCTR_MKT_ORG) and as_of_date='04-MAR-2004'
Select distinct a.pctr_num,b.pctr_num from TACCT_DAILY_DEPOSIT a , ALL_PCTR_MKT_ORG b
where a.primary_branch_num=b.pctr_num(+) and
b.pctr_num is null and a.as_of_date = '04-MAR-2004'

Say second query returns me with 2 pctr_nums (100,101).
But when i checked in ALL_PCTR_MKT_ORG those 2 pctr_nums are available.

Where am i going wrong? I want to list all Pctr_num in TACCT_DAILY_DEPOSIT and
NOT IN ALL_PCTR_MKT_ORG. Since data is huge in TACCT_DAILY_DEPOSIT
i'm going for one particular date.

any thoughts ?

Due to huge data first query (not in) takes lot of time, which made me to go for second one.

Thanks!!!!!!!

[Steve Steel]

Hi

Look at
http://dbforums.com/arch/11/2003/4/738850


Steve Steel

[Sanjay Kumar Suri]

Try the following:

Bring the + on the left:

Select distinct a.pctr_num, b.pctr_num
from TACCT_DAILY_DEPOSIT a ,
ALL_PCTR_MKT_ORG b
where a.primary_branch_num(+)=b.pctr_num
and b.pctr_num is null
and a.as_of_date (+) = '04-MAR-2004'

sks