Re: DECODE

"Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message news:406d072b_at_news.victoria.tc.ca...
| 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

the perils of multi-posting -- see the derivative thread over in c.d.o.server for a solution using TRUNC or ROUND

;-{ mcs Received on Fri Apr 02 2004 - 13:24:55 CEST