Re: DECODE -- nah, use round

"Douglas Hawthorne" <douglashawthorne_at_yahoo.com.au> wrote in message news:nD1bc.135809$Wa.110151_at_news-server.bigpond.net.au...
| "Sherman H." <shung_at_earthlink.net> wrote in message
| news:kA0bc.11648$lt2.2675_at_newsread1.news.pas.earthlink.net...
| > 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. . .
| >
| >
|
| Sherman,
|
| If you are using 9i and later, you can use the CASE clause
|
| SELECT
| trans_date,
| CASE
| WHEN trans_date BETWEEN '01-JUL-2001' AND '30-JUN-2002'
| THEN 'Fiscal2001'
| WHEN trans_date BETWEEN '01-JUL-2002' AND '30-JUN-2003'
| THEN 'Fiscal2002'
| WHEN trans_date BETWEEN '01-JUL-2003' AND '30-JUN-2004'
| THEN 'Fiscal2004'
| ELSE NULL
| END AS fiscal
| FROM
| test_dates
| ;
|
| TRANS_DAT FISCAL
| --------- ----------
| 30-JUN-02 Fiscal2001
| 01-JUL-02 Fiscal2002
| 30-JUN-03 Fiscal2002
| 01-JUL-03 Fiscal2004
|
| Douglas Hawthorne
|
|

actually, i bet this could be handled with the date version of the ROUND function

any takers? i'm on my way out, but i'll post a solution tomorrow that will translate any date to the proper fiscal year (unless someone else gets to it first)

;-{ mcs Received on Thu Apr 01 2004 - 17:43:49 CST