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

Home -> Community -> Usenet -> c.d.o.server -> Re: newbie: conditions in expressions

Re: newbie: conditions in expressions

From: Gerard M. Averill <e-mail.address_at_my.sig>
Date: Mon, 01 Feb 99 22:18:51 GMT
Message-ID: <79592s$aim$2@news.doit.wisc.edu>


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



Gerard M. Averill, Researcher
CHSRA, University of Wisconsin - Madison GAverill<at>chsra<dot>wisc<dot>edu Received on Mon Feb 01 1999 - 16:18:51 CST

Original text of this message

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