Nested query problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Nested query problem

  1. #1
    Join Date
    Dec 2000
    Posts
    41
    I could use some help with this query - I seem to have come to a dead end here. I'm trying to find users that exist in one table but do not exist in another table. In the first table, user names are initials, like 'TDC'. In the second table, they can exist in two different formats. I want the query to return (ultimately) the number of users that exist in the first table but not the second.

    If I run the following statement separately, I get results of 1 row. (User is TDC)

    select count(*) from
    (select distinct user_id from seat_assignment
    where s_add_on_cd='...' );

    When I run the following 2 statements separately, I get results that do not include user TDC:


    select distinct upper(substr(osuser,10,8))
    from v$session where
    lower(program) <> 'oracle.exe' and
    lower(program) <> 'jre.exe' and
    lower(program) <> 'impserver.exe' and
    lower(program) <> 'impadmin.exe' and
    lower(program) <> 'cfserver.exe' and
    lower(program) <> 'msaccess.exe' and
    lower(program) <> 'sqlplusw.exe' and
    lower(program) <> 'dbsnmp.exe' and
    lower(program) <> 'jrew.exe';

    select distinct upper(osuser) from v$session where
    lower(program) <> 'oracle.exe' and
    lower(program) <> 'jre.exe' and
    lower(program) <> 'impserver.exe' and
    lower(program) <> 'impadmin.exe' and
    lower(program) <> 'cfserver.exe' and
    lower(program) <> 'msaccess.exe' and
    lower(program) <> 'sqlplusw.exe' and
    lower(program) <> 'dbsnmp.exe' and
    lower(program) <> 'jrew.exe';

    However, when I combine all these statements together, I get a count of '0' when in fact I should get '1' for user TDC.


    select count(*) from
    (select distinct user_id from deltek.seat_assignment
    where s_add_on_cd='...' and
    user_id not in (select distinct upper(substr(osuser,10,8))
    from v$session where
    lower(program) <> 'oracle.exe' and
    lower(program) <> 'jre.exe' and
    lower(program) <> 'impserver.exe' and
    lower(program) <> 'impadmin.exe' and
    lower(program) <> 'cfserver.exe' and
    lower(program) <> 'msaccess.exe' and
    lower(program) <> 'sqlplusw.exe' and
    lower(program) <> 'dbsnmp.exe' and
    lower(program) <> 'jrew.exe') or
    user_id not in
    (select distinct upper(osuser) from v$session where
    lower(program) <> 'oracle.exe' and
    lower(program) <> 'jre.exe' and
    lower(program) <> 'impserver.exe' and
    lower(program) <> 'impadmin.exe' and
    lower(program) <> 'cfserver.exe' and
    lower(program) <> 'msaccess.exe' and
    lower(program) <> 'sqlplusw.exe' and
    lower(program) <> 'dbsnmp.exe' and
    lower(program) <> 'jrew.exe'));

    Does anyone have any ideas of why this is not working?

    Dawn


  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    I think it's something to do with the fact that your query

    select distinct upper(substr(osuser,10,8))
    from v$session where
    lower(program) <> 'oracle.exe' and
    lower(program) <> 'jre.exe' and
    lower(program) <> 'impserver.exe' and
    lower(program) <> 'impadmin.exe' and
    lower(program) <> 'cfserver.exe' and
    lower(program) <> 'msaccess.exe' and
    lower(program) <> 'sqlplusw.exe' and
    lower(program) <> 'dbsnmp.exe' and
    lower(program) <> 'jrew.exe'

    will return some null values for the "osusers" which have less than 10 characters in their names. This seems to cause the "not in" clause not to function correctly, possibly because comparing something to null returns null rather than true.

    I suggest you rewrite it to:

    select count(*) from
    (select distinct user_id from deltek.seat_assignment
    where s_add_on_cd='...' and
    user_id not in (select distinct upper(substr(osuser,10,8))
    from v$session where
    lower(program) <> 'oracle.exe' and
    lower(program) <> 'jre.exe' and
    lower(program) <> 'impserver.exe' and
    lower(program) <> 'impadmin.exe' and
    lower(program) <> 'cfserver.exe' and
    lower(program) <> 'msaccess.exe' and
    lower(program) <> 'sqlplusw.exe' and
    lower(program) <> 'dbsnmp.exe' and
    lower(program) <> 'jrew.exe'
    and length(osuser) > 10))

    I'm not sure what you're trying to do with the "or" bit of the query.

  3. #3
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    It's actually in the SQL manual:

    NOT IN

    Equivalent to "!=ALL". Evaluates to FALSE if any member of the set is NULL

  4. #4
    Join Date
    Dec 2000
    Posts
    41
    It worked! Thanks so much. I learned something new about the 'NOT IN'. Makes sense.

    The 'OR' was a typo - I'm actually using 'AND' and it works.

    Thanks for your help!

    Dawn

Posting Permissions

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


Click Here to Expand Forum to Full Width