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

From: Vamshi <yrg01_at_yahoo.com>
Date: 2 Nov 2001 00:28:21 -0800
Message-ID: <b97132c2.0111020028.343776cd@posting.google.com>

Richard Kuhler <noone_at_nowhere.com> wrote in message news:<mOVD7.11291$D5.2923203_at_typhoon.san.rr.com>...
> <snip>
>
> > On earlier versions of Oracle, this usually turns
> > into a messy sequence of subtracting sysdate
> > and decoding the sign() function applied to the
> > result.
>
> He's referring to something like this:
>
> select decode(col4,
> 'ABC', decode(sign(least(sysdate - date1, date2 - sysdate)),
> 1, 'DEF YES', 'DEF NO'),
> 'DEF', decode(sign(least(sysdate - date1, date2 - sysdate)),
> 1, 'DEF YES', 'DEF NO'),
> 'NEITHER ABC NOR DEF')
> from t1
>
>
> Richard

Hi Richard & Jonathan,
Thanks for the smart & elegant 'DECODE Logic'. I'm gonna use the code.

I'm a kind of worried as I'll be using 4 tables in the query & they are very big in *Production* environment. A couple of tables them have 17 to 20 million rows. ***Any way that's a different issue***

I'm using Oracle 8i (8.1.6...)

Actually my wife suggested a different solution... not sure on the efficiency
Well the query looks as below:

select decode(col4,
  'ABC', case when date1 < sysdate and date2 > sysdate

     then 'DEF YES' 
     else 'DEF NO' end,
  'DEF', case when date1 < sysdate and date2 > sysdate 
     then 'DEF YES' 
     else 'DEF NO' end,

  'NEITHER ABC NOR DEF')
from t1

=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~ 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???

Thanks & Cheers,
Vamshi Reddy Received on Fri Nov 02 2001 - 02:28:21 CST