Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19

Thread: SQL sets query

  1. #16
    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    14

    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

  2. #17
    Join Date
    Mar 2003
    Location
    NJ/NY
    Posts
    17

    Thumbs up

    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.

  3. #18
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    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.

  4. #19
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201

    Cool

    Congratulation!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •