RE: CASE in PL/SQL

Hi George,

With this solution you can only find Quarter number. but I also need Year of that quarter number.
when you say to_char(date,'Q'), it might go next year or stay in current fiscal year depending on number of months we add to the date.

Thanks,
Surendra

-----Original Message-----
Sent: Wednesday, July 02, 2003 12:56 PM
To: Multiple recipients of list ORACLE-L

You don't need CASE.
try :
to_char( date , 'Q')

George

> Hello ALL,
>
> I am trying to find quarter number from a given date . Here is the
> description
>
> Our Financial year runs from July thru June. So, Given any date between
> these dates I need to find 3 quarters(9 months) from it.
> July -Sep -> 1st Quarter
> Oct -Dec -> 2nd Quarter
> Jan -Mar -> 3rd Quarter
> Apr -Jun -> 4th Quarter
>
> I got this done using the following Select
>
> select decode(to_char(add_months(sysdate,9),'Q'),
> '1','3',
> '2','4',
> '3','1',
> '4','2',
> '')
>
> Decode function is used to change calender quarter to our Quarter.
>
> But I am unable to find the Year for that quarter.
> I was using Case statement to solve my problem,as below
>
> Select case when decode(to_char(add_months(sysdate,9),'Q'),
> '1','3',
> '2','4',
> '3','1',
> '4','2',
> '') < 3
> then to_number(to_char(main_rec.termination_date,''YYYY''))+1
> else to_number(to_char(main_rec.termination_date,''YYYY''))
>
> P.S The reason for <3 condition check in CASE Statement is, if a
> sysdate+9months falls in next Financial year , I need to change Year
> accordingly.
>
> But,this works only in SQL, in Procedures, i cannot do this using CASE
> Statement
>
> Can anybody give some ideas on how to approach this?. I have to use this
in
> a cursor (not in the body of my procedure, so condition checking like "If
> then else " after fetching year is not possible)
>
> Any help would be greatly appreciated.
>
> Thanks,
> Surendra Tirumala
> Database Administrator
> Cabinet for Workforce Development
> Commonwealth of Kentucky
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: George Oneata
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).