Re: IF-like function in Oracle?

Chuck Hamilton (chuckh_at_ix.netcom.com) wrote:
> Is there an IF like function for SQL. I want to do something like
> this...
> 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?
> --
> Chuck Hamilton
> chuckh_at_ix.netcom.com

The answer is No

     BUT      you can simulate it by playing with the decode function.

     DECODE function can only test for equality-else type of 
     conditions. More complicated
     conditions like: 

     IF ( {something} < | > {something_else}, 
              return this else return that) 

     cannot be coded directly into a DECODE. 

     For such cases you can use basic arithmetic 
     operations in conjunction with numeric functions
     to simulate the behaviour. eg. to write a 
     MIN function in ORACLE, you may do the following:

     To find the minimum of n1 and n2:
     select DECODE( floor(n1/n2), 0, n1, n2 ) from dual;

     To do something like:
     if      n1 between x1 and x2 return 'M'
     else if n1 between y1 and y2 return 'N'
     else return 'O'
     you may say:

     select DECODE( floor( (n1-x1) / (x2-n1+1) ), 0, 'M',
               DECODE( floor( (n1-y1) / (y2-n1+1) ), 0, 'N', 'O')
                  )
     from dual;

     Similarly you can write a MAX function, or simulate any 
     such IF function. It would be harder
     to manipulate strings in this way. 

--
**************************************************************
*                          Saad Ahmad                        *
*                          McHugh Freeman & Associates, Inc. *
*                          Ph:  (414) 798-8606 Ext. 457      *
*                          Fax: (414) 798-8619               *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************