Re: DECODE OR CASE

From: Bagieta <bagieta21_at_poczta.onet.pl>
Date: Fri, 2 Jul 2004 10:34:38 +0200
Message-ID: <cc3706$c6e$1@news.onet.pl>

Yes you can use decode f.ex but there is a lot of code you have to write. To compare values you may use DECODE and SIGN

SIGN takes a numeric argument and returns either 1 if the argument is > 0
0 if the argument = 0
-1 if the argument is < 0

So you can use comparing expressions like those

X>=0 SIGN(SIGN(X)+1)
X<=0 SIGN(1-SIGN(X))

X>0 (1-SIGN(1-SIGN(X)))
X<0 (1-SIGN(SIGN(X)+1))
X=0 (1-ABS(SIGN(X)))

X >=Y SIGN(SIGN(X-Y)+1)
X<=Y SIGN(1-SIGN(X-Y))

X>Y (1-SIGN(1-SIGN(X-Y)))
X<Y (1-SIGN(SIGN(X-Y)+1))
X=Y (1-ABS(SIGN(X-Y)))

X!=Y ABS(SIGN(X-Y)) and have something like this
SELECT val, DECODE((1-SIGN(1-SIGN(val - 10))),1, '>10', '<=10') result FROM table

Of course your queries will be more complicated.

Regards
Bagieta
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

dbDeveloper - Multiple databases editor

http://prominentus.com
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Received on Fri Jul 02 2004 - 03:34:38 CDT