[sqr-users] Réf. : [sqr-users] What twabbling me... NOTEXIST and DISTINCT MAX

["Thaî Le Tat" <thai.le-tat@creditlyonnais.fr>]

It looks like an Oracle bug.

1/ Can you try the following, and give us the results ?

      SQL> select * from dual where 'a'='b';
      no rows selected

      SQL>  select sysdate from dual;
      SYSDATE
      ---------
      16-JAN-03

      SQL>  select sysdate from dual where not exists (select * from dual where 
'a'='b');
      SYSDATE
      ---------
      16-JAN-03

2/ You can replace your NOT EXISTS  with an NOT IN   ( if  EMPLID is never null 
(or empty) in PS_CUSTOM ).

Regards,
Thai LE TAT






Ken Boettger <BoettgeK@cwu.EDU>@sqrug.org le 15/01/2003 01:53:13

Veuillez répondre à sqr-users@sqrug.org

Envoyé par :      sqr-users-admin@sqrug.org


Pour : sqr-users@sqrug.org
cc :    (ccc : Thaî Le Tat/DSP/DSTI/CLY)

Objet :     [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





Ce message contient des informations confidentielles ou appartenant au
Crédit Lyonnais et est établi à l'intention exclusive de ses
destinataires. Toute divulgation, utilisation, diffusion ou reproduction
(totale ou partielle) de ce message, ou des informations qu'il contient,
doit être préalablement autorisée. Tout message électronique est
susceptible d'altération et son intégrité ne peut être assurée.
Le Crédit Lyonnais décline toute responsabilité au titre de ce
message s'il a été modifié ou falsifié. Si vous n'êtes pas
destinataire de ce message, merci de le détruire immédiatement et
d'avertir l'expéditeur de l'erreur de distribution et de la destruction
du message.
Crédit Lyonnais, SA au capital de Euros 1.807.874.827 - RCS Lyon B 954 509 741
Siège Central : 19, boulevard des Italiens. 75002 Paris. France

This e-mail contains confidential information or information belonging
to Crédit Lyonnais and is intended solely for the addressees.
The unauthorised disclosure, use, dissemination or copying (either whole
or partial) of this e-mail, or any information it contains, is prohibited.
E-mails are susceptible to alteration and their integrity cannot be guaranteed.
Crédit Lyonnais shall not be liable for this e-mail if modified or falsified.
If you are not the intended recipient of this e-mail, please delete it
immediately from your system and notify the sender of the wrong delivery
and the mail deletion.
Crédit Lyonnais SA.
Share Capital of Euros 1.807.874.827.
Registered Office : Lyon (B 954 509 741)
Central and administrative Office : 19, boulevard des Italiens. 75002 Paris. 
France