DECODE

[Sherman H.]

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

[Malcolm Dew-Jones]

Sherman H. (shung@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

[Mark C. Stock]

"Malcolm Dew-Jones" <yf110@vtn1.victoria.tc.ca> wrote in message
news:406d072b@news.victoria.tc.ca...
| Sherman H. (shung@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