Re: IF-like function in Oracle?

chuckh_at_ix.netcom.com (Chuck Hamilton) wrote:

>IF X > Y
> THEN return value A as the column's value
> ELSE return value B as the column's value
 

>I can use DECODE() if I just want to test for simple coditions like =
>or <>, but I don't know how to make it handle >, <, >=, <=. Do I have
>to write my own custom functions to do this?

You could of course use PL/SQL, but if you insist on DECODE, here is a way of doing it:

DECODE(SIGN(x-y), 0,value_if_x_equals_y
                ,-1,value_if_x_smaller_y
                , 1,value_if_x_greater_y)

This should work. I tried it with dates to retrieve the sum of sales in three different periods in one go (this month, the last 12 months and from Jan u/i this month). It works great, but if you have PL/SQL you have a much more transparent statement. Unless, of course, you would want nobody else but you to be able to decipher your scripts ;-)

+------------------------------------------------------------+
|   Mike Philippens - Gorinchem - The Netherlands, Europe    |
|                 Vijfhart Automatisering bv                 |
|      Oracle Specialists in Training and Consultancy        |
|                 Utrecht    The Netherlands                 |
+------------------------------------------------------------+