Re: *DECODE* to implement IF condition???

Vamshi wrote:

<snip>

> select DECODE ( T1.col4,'ABC', DECODE ( (SELECT 'ABC_YES' from dual
> where sysdate between date1 and date2),
> 'ABC_YES', 'ABC YES',
> 'ABC_NO'
> ),
> 'DEF', DECODE ( (SELECT 'DEF_YES' from dual where sysdate
> between date1 and date2),
> 'DEF_YES', 'DEF YES',
> 'DEF NO'
> ),
> null --Neither ABC nor DEF
> )
> from SOME_TABLE T1
>
> =~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~
> Any pitfalls/inefficiencies???

Remember, the only reason I provided the sign/decode solution after the original 'case' solution was because the case statement won't work in pl/sql. Your subquery in the select clause version won't work in pl/sql either (not in 8i anyways). Also, this new query actual has a different condition than the original. The 'between' operator is inclusive so your new query does <= & >= where the original did < & >. My original instinct was that with the two additional subqueries, this would be slower. However, I'm surprised by this quick benchmark I did:

case: 14.46 seconds (ouch!)
sign/decode: 2.5 seconds
subquery: 1.9 seconds

From the looks of that 'case' version, I may never use that statement again. I don't have time right now to profile these and find out what the issues are but I'm definitely curious so I probably will later.

Richard Received on Fri Nov 02 2001 - 10:44:17 CST