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

["Alexander, Steve" <Steve.Alexander@ci.sj.ca.us>]

I don't use the exists feature too often, but I think you may have to select
a single field or literal, rather than all fields;

... where not exists (select 'X' from ...



-----Original Message-----
From: Ken Boettger [mailto:BoettgeK@cwu.edu]
Sent: Tuesday, January 14, 2003 4:53 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] What twabbling me... NOT EXIST and DISTINCT MAX


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

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