From Surendra.Tirumala@mail.state.ky.us Wed, 02 Jul 2003 14:26:33 -0700 From: Surendra.Tirumala@mail.state.ky.us Date: Wed, 02 Jul 2003 14:26:33 -0700 Subject: RE: CASE in PL/SQL Message-ID: MIME-Version: 1.0 Content-Type: text/plain Title: RE: CASE in PL/SQL I am already using it that way, but giving that condition in cursor is not possible. Thanks for your help. Surendra -----Original Message-----From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 02, 2003 5:01 PMTo: Multiple recipients of list ORACLE-LSubject: RE: CASE in PL/SQL just use the sql to assign value to your variable ... if you really want my advise, make this a function or a procedure so you can call it from where ever you want. As function can be completely written in pl/sql you should be okay ... -- this is a procedure ... create or replace PROCEDURE    dbp_calc_fin_qtr (pi_date in date, po_qtr number, po_year out number) is   nCurrQtr      pls_integer := to_number(to_char(pi_date,'Q'));   nFinQtr   pls_integer := 0;   nFinYear  pls_integer := to_number(to_char(pi_date,'YYYY')); begin   if nCurrQtr in (1,2) then     nFinQtr  := nCurrQtr + 2;   else     nFinQtr  := nCurrQtr - 2;     nFinYear := nFinYear + 1;   end if;   -- end dbp_calc_fin_qtr; / -- this functions returns following string ... -- QQYYYY where QQ is financial qtr and YYYY is financial year create or replace FUNCTION   dbp_calc_fin_qtryr (pi_date in date, po_qtr number, po_year out number) return varchar2 is   nCurrQtr      pls_integer := to_number(to_char(pi_date,'Q'));   nFinQtr   pls_integer := 0;   nFinYear  pls_integer := to_number(to_char(pi_date,'YYYY')); begin   if nCurrQtr in (1,2) then     nFinQtr  := nCurrQtr + 2;   else     nFinQtr  := nCurrQtr - 2;     nFinYear := nFinYear + 1;   end if;   --   return (to_char(nFinQtr,'09') || to_char(nFinYear));   -- end dbp_calc_fin_qtryr; / Raj -------------------------------------------------------------------------------- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 02, 2003 4:06 PM To: Multiple recipients of list ORACLE-L Subject: 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).