[sqr-users] What twabbling me... NOT EXIST and DISTINCT MAX

[Ken Boettger <BoettgeK@cwu.EDU>]

Platform: UNIX

I have two questions with SQL related to a SQR I am working on.  The SQR is 
failing so I took the SQL statements out to run them directly in Oracle.  The 
questions are best explained by simple examples. So here goes...


Example #1, simple select 

select  Mb.EFFDT
FROM PS_PERS_DATA_EFFDT Mb
WHERE Mb.EMPLID = '10057607'


EFFDT
-----------
02-JUN-1997
20-DEC-2002

***********************************

Example #2, simple select with NOT EXISTS clause (there are no records in the 
PS_CUSTOM)

select  Mb.EFFDT
FROM PS_PERS_DATA_EFFDT Mb
WHERE NOT EXISTS (SELECT * FROM PS_CUSTOM PCR WHERE PCR.EMPLID = Mb.EMPLID)
and Mb.EMPLID = '10057607'

no rows selected

Question #1: The first example proves that there is data to be selected, but we 
lose it by adding the NOT EXISTS clause.  Since there are no records in PCR, 
the NOT EXISTS should evaluate to true and generate the same thing as example 
#1. Why do I get the result "no records selected"? Can someone help?

***********************************

Example #3, simple select using distinct and maximum

select  distinct MAX(Mb.EFFDT)
FROM PS_PERS_DATA_EFFDT Mb
WHERE Mb.EMPLID = '10057607'


MAX(MB.EFFDT)
------------------------
20-DEC-2002

***********************************

Example #4, same as example #3 but with the NOT EXIST clause added (again, PCR 
has no records or data).

select  distinct MAX(Mb.EFFDT)
FROM PS_PERS_DATA_EFFDT Mb
WHERE NOT EXISTS (SELECT * FROM PS_CUSTOM PCR WHERE PCR.EMPLID = Mb.EMPLID)
and Mb.EMPLID = '10057607'

MAX(MB.EFFDT)
-------------------------


Note that it I get something returned but no actual data. Almost as if I 
recieved a null value as a result set.

Question #2:  Can someone explain what is happening here?  I added the NOT 
EXISTS clause to my select statement and I would think I would still return the 
20-DEC-2002 (again, there are no records in PCR so the NOT EXISTS should 
evaluate to a true state and Example #3 and #4 should be identical in what they 
return.

Ken Boettger
boettgek@cwu.edu
http://www.cwu.edu/~boettgek
x2944


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users