Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: newbie: conditions in expressions
In article <36b2c65c.1058862_at_bigXb>, uk7i_at_rz.uni-karlsruhe.de wrote:
>
>i seem to miss some very basic concept in oracle, but how can i use
>conditions in select-expressions?
>
Since boolean expressions cannot appear in the select list in Oracle's
implementation of SQL-SELECT statements, they must be emulated using one of
two approaches: user-defined functions, structured appropriately so that they
can be called by SQL-SELECT statements; or expressions of Oracle built-in
functions. The former has the advantage of being more readable; for instance,
to test two operands for equality, one could write a user-defined function
IS_EQUAL which would return 1 if the operands are equal, 0 in not equal, or
NULL if one or more operands is missing. Expressions using built-in functions
have a definite speed advantage, even where several function calls are
involved, although readability is sacrificed. In any case, here are a list of
some relational and logical operations which can be implemented using
expressions of built-in functions.
Relational Operators:
A = B
Num, Date operands: 1 - abs(sign(A - B))
Char operands: decode(null, A, null, B, null, decode(A, B, 1, 0))
A <> B
Num, Date operands: abs(sign(A - B))
Char operands: decode(null, A, null, B, null, decode(A, B, 0, 1))
A <= B
Num, Date operands: sign(1 - sign(A - B))
Char operands: decode(null, A, null, B, null, decode(least(A, B), A, 1, 0))
A > B
Num, Date operands: 1 - sign(1 - sign(A - B))
Char operands: decode(null, A, null, B, null, decode(least(A, B), A, 0, 1))
A >= B
Num, Date operands: sign(1 + sign(A - B))
Char operands: decode(null, A, null, B, null, decode(greatest(A, B), A, 1, 0))
A < B
Num, Date operands: 1 - sign(1 + sign(A - B))
Char operands: decode(null, A, null, B, null, decode(greatest(A, B), A, 0, 1))
Logical Operators (operands in 0 (false), 1 (true), or null):
A and B
decode(0, A, 0, B, 0, A * B)
A or B
decode(1, A, 1, B, 1, A + B)
not A
1 - A
Note that AND will return 0 (false) if either of the operands is false (even if the other is null), and OR will return 1 (true) if either of the operands is true (even if the other is null).
Hope this helps.
Gerard