[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Re: [sqr-users] Réf._:_[sqr-users]_What_twabbling_me..._NOTEXIST and DISTINCT MAX
I don't see where the contradiction is.
NOT EXISTS (select * from dual where 'a'='b')
evaluates to True and therefore you get rows.
If you change this to EXISTS, then you will get no
rows.
select sysdate from dual where exists (select * from
dual where 'a'='b')
no rows selected
--- Thaî Le Tat <thai.le-tat@creditlyonnais.fr> wrote:
>
>
> 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
>
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users