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