-
Try the following
Select a.id, c.name
from
(Select accnt.id from accnt Where accnt.ID IN
(SELECT acct_id FROM opty where prob = 100 AND opty.ROW_ID IN
(SELECT opty_id FROM opty_postn WHERE close_dt<1/1/2002)
)) a JOIN
(Select accnt.id from accnt Where accnt.ID NOT IN
(SELECT acct_id FROM opty where prob = 100 AND opty.ROW_ID IN
(SELECT opty_id FROM opty_postn WHERE close_dt<1/1/2002)
)) b ON a.id = b.id
JOIN accnt c ON a.id = c.id
-
Claire,
Got the script to work.
The reason it didnt work is that there were some rows with accnt_id as NULL.
So if I add a condition in both sub queries to check for accnt_id IS NOT NULL, it works.
Query B gave out a couple of NULL records, and due to that NOT IN ( <> ALL) would compare every value to NULL and hence get no result at all.
Thanks for your help.
-
Ash,
Actually I even asked mak for help.He also said your query should work.So we could only guess the problem come from the table it self.
I am sorry I couldnt give you solid help.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|