Re: Query Help

From: <fitzjarrell_at_cox.net>
Date: Thu, 17 Jan 2008 09:28:51 -0800 (PST)
Message-ID: <91cd2b06-8c5a-44ff-be0b-8744882015a8@e25g2000prg.googlegroups.com>

Comments embedded.
On Jan 17, 7:05 am, spamb..._at_milmac.com (Doug Miller) wrote:
> In article <1c892130-538b-49bd-a6dc-b098222ee..._at_x69g2000hsx.googlegroups.com>, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
> >Just a minor correction to my previous post... additional thoughts
> >shortly after submitting the previous post.  These two logic checks
> >are not necessarily equivalent when dealing with data stored in a
> >database, but are equivalent when dealing only with Boolean logic:
> >IF (AGE <> 65) OR (AGE = 65 AND RETIRED = "Y") THEN
>
> >IF (AGE <> 65) OR (RETIRED = "Y") THEN
>
> >The first expression does not permit NULL values for the AGE, while
> >the second does.  
>
> I disagree:

And you're free to do so, but your disagreement is in error.

> if AGE is NULL, then the first clause (AGE <> 65) is true,

No, it isn't:

SQL> set serveroutput on size 100000
SQL> select * From agetest;

       AGE R
---------- -

        65 Y
           Y

2 rows selected.

Notice that AGE is NULL in one of the records. By your logic the following code would return one record --

SQL> begin

  2     for a in (select * from agetest) loop
  3             if a.age <> 65 then
  4                     dbms_output.put_line(a.age||' *********** '||
a.retired);
  5             end if;
  6     end loop;

  7 end;
  8 /

PL/SQL procedure successfully completed.

SQL> yet it doesn't. Thus the condition AGE <> 65 is FALSE for all NULL values of AGE. Imagine that.

> and in
> either form, the second clause won't even be evaluated (due to the
> conjunction).
>

Your 'logic' escapes me, and it also escapes Oracle:

SQL> begin

  2     for a in (select * from agetest) loop
  3             if (a.AGE <> 65) OR (a.RETIRED = 'Y')  then
  4                     dbms_output.put_line(a.age||' *********** '||
a.retired);
  5             end if;
  6     end loop;

  7 end;
  8 /
65 *********** Y
*********** Y

PL/SQL procedure successfully completed.

SQL> The second clause IS evaluated, as evidenced by the output above, and provides the output displayed. Let's try the first condition Charles provided and see if it works:

SQL> begin

  2     for a in (select * from agetest) loop
  3             if (a.AGE <> 65) OR (a.AGE = 65 AND a.RETIRED = 'Y')
then
  4                     dbms_output.put_line(a.age||' *********** '||
a.retired);
  5             end if;
  6     end loop;

  7 end;
  8 /
65 *********** Y

PL/SQL procedure successfully completed.

SQL> Notice it's the SECOND clause again providing the output. So it does get evaluated. Also note that Charles wrote correctly that the above conditional does not 'cater' to NULL values, as only one of the two rows is displayed.

> --
> Regards,
>         Doug Miller (alphageek at milmac dot com)
>
> It's time to throw all their damned tea in the harbor again.

David Fitzjarrell Received on Thu Jan 17 2008 - 11:28:51 CST