Re: DECODE

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Thu, 01 Apr 2004 23:31:31 GMT
Message-ID: <nD1bc.135809$Wa.110151@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 Received on Thu Apr 01 2004 - 17:31:31 CST