-
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
-
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.
-
It's actually in the SQL manual:
NOT IN
Equivalent to "!=ALL". Evaluates to FALSE if any member of the set is NULL
-
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
