Re: Useful Oracle books - C.J. Date theory vs. practicality

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Fri, 28 May 2004 14:00:50 -0600
Message-id: <40B79A72.8010701@sun.com>

Here is sql to show the 3 valued logic in Oracle (I don't have nor do I want access to ProCobol to test)

create table three_val_logic (test_3_val char(5));

insert into three_val_logic values ('TRUE');
insert into three_val_logic values ('FALSE')
insert into three_val_logic values (NULL)

commit;

select * from three_val_logic;

TEST_

---

TRUE
FALSE select rownum, test_3_val
from three_val_logic
where test_3_val = 'TRUE';

     ROWNUM TEST_
---------- -----

          1 TRUE select rownum, test_3_val
from three_val_logic
where test_3_val = 'FALSE';

     ROWNUM TEST_
---------- -----

          1 FALSE select rownum, test_3_val
from three_val_logic
where test_3_val != 'TRUE';

     ROWNUM TEST_
---------- -----

          1 FALSE select rownum, test_3_val
from three_val_logic
where test_3_val != 'FALSE';

     ROWNUM TEST_
---------- -----

          1 TRUE select rownum, test_3_val
from three_val_logic
where test_3_val IS NOT NULL;

     ROWNUM TEST_
---------- -----

          1 TRUE
          2 FALSE

pl/sql version
  1 declare

   2     cursor three_val_cur is select rownum, test_3_val from three_val_logic;
   3     three_val_rec three_val_cur%rowtype;
   4  begin
   5     for three_val_rec in three_val_cur
   6     loop
   7        if three_val_rec.test_3_val = 'TRUE'
   8        then
   9           dbms_output.put_line('Line '||three_val_rec.rownum||' is TRUE');
  10        elsif three_val_rec.test_3_val = 'FALSE'
  11        then
  12           dbms_output.put_line('Line '||three_val_rec.rownum||' is FALSE');
  13        else
  14           dbms_output.put_line('Line '||three_val_rec.rownum||' is not TRUE and not FALSE');
  15        end if;
  16        if three_val_rec.test_3_val != 'TRUE'
  17        then
  18           dbms_output.put_line('Line '||three_val_rec.rownum||' is NOT TRUE');
  19        elsif three_val_rec.test_3_val != 'FALSE'
  20        then
  21           dbms_output.put_line('Line '||three_val_rec.rownum||' is NOT FALSE');
  22        else
  23           dbms_output.put_line('Line '||three_val_rec.rownum||' is not NOT TRUE and not NOT FALSE');
  24        end if;
  25     end loop;

  26* end;
SQL> /
Line 1 is TRUE
Line 1 is NOT FALSE
Line 2 is FALSE
Line 2 is NOT TRUE
Line 3 is not TRUE and not FALSE
Line 3 is not NOT TRUE and not NOT FALSE

So a NULL in pl/sql fails the equality comparison test. Its the not NOT TRUE and not NOT FALSE which seem to mess people up. In 2 valued logic NOT TRUE = FALSE and NOT FALSE = TRUE, but this is not the case in 3 valued logic.

Enjoy the Ardberg... I'm jonesing for The Glenmorangie Burgundy Wood Finish or 18 year old Highland Park.

Daniel

Jared.Still_at_radisys.com wrote:
>
>
> > just for fun, check out these two commands:
> >
> > IF (some condition) THEN (statement 1) ELSE (statement 2);
> > IF NOT (some condition) THEN (statement 2) ELSE (statement 1);
> >
> > regardless the contents of your data structures,
> > in COBOL these two are equivalent but in PL/SQL they are not!
>
> You've really lost me here.
>
> 12:22:41 rsysdevdb.radisys.com - jkstill_at_dv01 SQL>
> 12:22:46 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> l
> 1 begin
> 2
> 3 if true then
> 4 dbms_output.put_line('TRUE');
> 5 else
> 6 dbms_output.put_line('FALSE');
> 7 end if;
> 8
> 9 if not true then
> 10 dbms_output.put_line('FALSE');
> 11 else
> 12 dbms_output.put_line('TRUE');
> 13 end if;
> 14
> 15* end;
> 12:22:47 rsysdevdb.radisys.com - jkstill_at_dv01 SQL> /
> TRUE
> TRUE
>
> PL/SQL procedure successfully completed.
>
> Maybe I need a slug of Ardberg to understand.
>
>
> Jared

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri May 28 2004 - 14:58:11 CDT