Re: DECODE or else, a little test...

L. Tseng wrote:
>
> DECODE() is probably the most powerful function in Oracle.
>
> For example
>
>
> IF colA = 1 AND colB = 1 THEN 'YES'
> ELSE 'NO'
>
> can be converted as
>
> DECODE(colA, 1, DECODE(colB, 1, 'YES', 'NO'), 'NO')
>
> BUT.....
>
> Can DECODE do the following?
>
> IF colA > 100 THEN 'A'
> ELSIF colA = 100 THEN 'B'
> ELSE 'C'
>
> Thanks,
>
> Leslie
>
Yes if you mix decode with the sign function. Try this decode(sign(colA-100),1,'A',0,'B','C') The function sign does the following: if colA < 100 (colA-100

becomes                                       negative) it returns -1
                                      if colA > 100 (colA-100
becomes                                       positive) it returns 1
                                      if colA = 100 (colA-100 becomes
0) 									                                      it returns 0