Re: DECODE

[Quoted] Sherman H. (shung_at_earthlink.net) wrote:
: I have to run a query to give a column a value based on a time range. Can I
: use DECODE?

: select decode(trans_date, trans_date>='01-Jul-2002' and
: trans_date<='30-Jun-2003','Fiscal2002', .....) as fiscal,
: from. . .
: where. . .

I believe that the newer versions of oracle support more complex logic than decode, i.e. case/choice expressions but I don't recall the names involved. That would be my first choice.

You could also write a function and use that to return the value.

Or use decode. The usual trick is to subtract one number from another to get either a positive, negative, or zero difference, and then use sign() to force that to be -1, 0, or 1, which decode can handle.

With a date, I am thinking you could convert it into seconds, or perhaps use numeric formats of the date that would work as numbers, i.e. 01-Jul-2002 becomes 2002 07 01 which becomes 20020701, which can be used as a number and compared to other dates like

e.g. (not your logic at all, just example of what I mean)

	decode( sign( 20020701 - 20030630 )  -- if 20020701 < 20030630 
	        , -1 , 'Fiscal2002'	     -- then this
		, 0  , 'something else       -- elsif = then this (error?)
	        ,      'not fiscal           -- else this case

	      )

(The numbers would come from date conversion functions)

$0.02 Received on Fri Apr 02 2004 - 08:24:43 CEST