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

Try

DECODE( (colA-100)/DECODE(colA,100,1,ABS(colA-100)),1,'A',0,'B','C')

i.e. divide (colA-100) by its magnitude, unless if colA = 100, in which case divide (colA-100) by 1, which yields 0/1 = 0 (have to avoid 0/0).

Rob Johnson

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
>
Received on Tue Nov 12 1996 - 00:00:00 CET