cancel
Showing results for 
Search instead for 
Did you mean: 

NOT IN QUERY

Prabhu_7
Frequent Advisor

NOT IN QUERY

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!!!!!!!
2 REPLIES
Steve Steel
Honored Contributor

Re: NOT IN QUERY

Hi

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


Steve Steel
If you want truly to understand something, try to change it. (Kurt Lewin)
Sanjay Kumar Suri
Honored Contributor

Re: NOT IN QUERY

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
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.