Re: IF-like function in Oracle?

mikephil_at_metropolis.nl (Mike Philippens) wrote:

>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 ;-)

Hey, anything that was hard to write ought to be hard to read too! That's my motto. :-)

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Incoming fire has the right of way!