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

From: Mark Styles <lambic_at_msn.com>
Date: 1996/11/13
Message-ID: <328A1974.4DCF_at_msn.com>#1/1


Gopinath Athilat wrote:
> L. Tseng wrote:
> >
> > DECODE() is probably the most powerful function in Oracle.
> > BUT.....
> >
> > Can DECODE do the following?
> >
> > IF colA > 100 THEN 'A'
> > ELSIF colA = 100 THEN 'B'
> > ELSE 'C'
> >
> DECODE and SIGN would do the trick!

Here's a bunch of ways to use decode:

Condition              
DECODE_condition                                       
---------------------- 
---------------------------------                      
A<B                     Decode( Sign(A-B), -1, 1, 0 )
A<=B                    Decode( Sign(A-B), 1, 0, 1 ) 
A>B                     Decode( Sign(A-B), 1, 1, 0 )  
A>=B                    Decode( Sign(A-B), -1, 0, 1 ) 
A=B                     Decode( A, B, 1, 0 )          
A between B and C       Decode( Sign(A-B), -1, 0, Decode(Sign(A-C), 1,
0, 1 )) 
A is null               Decode(A,null,1,0)          
A is not null           Decode(A,null,0,1)          
A in (B1,B2,...,Bn)     Decode(A,B1,1,B2,1,...,Bn,1,0)
Received on Wed Nov 13 1996 - 00:00:00 CET

Original text of this message