Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Expression

Re: Expression

From: Vladimir Andreev <1meter_at_beer.com>
Date: 2 Apr 2002 01:35:27 -0800
Message-ID: <e2683c0b.0204020135.401d178e@posting.google.com>


The Boolean datatype only exists in PL/SQL, that is, in stored procedures. This means that you cannot use expressions that evaluate to Boolean in SQL in other places than in predicates (WHERE..., HAVING...), and you cannot (for example) declare a table column of Boolean type. Hence, the statement will not compile, as you and some other people already noticed.

In PL/SQL, you can only assign Boolean values to variables of Boolean datatype, and use such variables and expressions in places where a condition is expected (IFs, EXIT WHENs, etc.). There is no way to explicitly or imlicitly convert Boolean to another datatype.

This said, you can still write the "smart" (but often unreadable) code C folks are used to. You just need to decode the Boolean to something known to SQL. The most common conversion needs are covered by the NVL function (to check for NULLs) and the "decode expression" (SQL only):
"y:=NVL(x,5);" and "select decode(x,null,5,x) into y from dual;"
are both equivalent to
if x is null then y:=5; else y:=x; end if;
"Decode" can convert any (non-) equality boolean expression into a
value, thus
decode(x,1,'A',2,'B','C') lets you "convert" statemens like if x=1 then result:='A'; elsif x=2 then result:='B'; else result:='C'; end if;
into a single expression, which is not necessarily more readable.

Other predicates might be converted to expressions using arithmetical
"tricks" when numbers are involved. Thus, your expression (A>B) may
have whatever value you decide, assuming A and B are numbers, using this expression in SQL (select it from dual if you need to use in PL/SQL):
decode(sign(A-B),1,'A is greater',0,'A equals B','B is greater')

Oracle 8i goes a step further by finally implementing the ANSI SQL
"case" expression, so you can now "convert" any predicate to a value:

select case when A>B then 1 when A=B then null else 0 end into result
from dual;

Cheers,
Flado Received on Tue Apr 02 2002 - 03:35:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US