Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Expression
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