Re: CASE statement implemented?

"Stefan Sandow" <stefan.sandow_at_gmx.de> wrote (in <7arvls$3q5$1_at_black.news.nacamar.net>)...

| Hi there,
| I desparate need to know if there is a CASE statement implemented in Oracle
| 8, where you can evaluate expressions like the following:
|
| CASE
| WHEN
| (a>b)
| then 'greater'
| else
| 'not greater'
| end
| I know that there is a DECODE, BUT..
| It can't do things like:
| DECODE((a>b), True, 'greater', 'not greater')
|
| Thanks for any usefull suggestion!
| Stefan
|

Hello Stefan,
 You can use 'DECODE' for the numeric comparison you require but it is not as easy. The 'trick' is to use the SIGN function which returns one of four values:
-1 : when value is negative,
0 : when value is zero
+1 : when value is positive
null : when value is null

Assuming 'a' and 'b' are numeric then to emulate a '>' test, just subtract the values and convert the result using the SIGN function. this gives:
DECODE( SIGN( a - b ),

        null, 'A and/or B is null',
        -1,   'B Greater then A',
         0,   'B equal to A',
        +1,   'A greater than 'B'  
      )

Also, the technique of using the SIGN function to limit the range of returned values can be useful in string matching.

For example the INSTR functions returns the position of a matching substring or 0 (zero) in no match. the SIGN function can be used force the result to be zero or one.

graham Received on Sat Feb 27 1999 - 06:13:15 CST