RE: CASE in PL/SQL

From: <Surendra.Tirumala_at_mail.state.ky.us>
Date: Wed, 02 Jul 2003 14:26:33 -0700
Message-ID: <F001.005C239B.20030702141044@fatcity.com>

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] [<A
href="mailto:[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: <A target=_blank href="http://www.orafaq.net">http://www.orafaq.net > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 <A target=_blank href="http://www.fatcity.com">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: <A target=_blank href="http://www.orafaq.net">http://www.orafaq.net -- Author: George Oneata INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 <A target=_blank href="http://www.fatcity.com">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: <A target=_blank href="http://www.orafaq.net">http://www.orafaq.net -- Author: INET:
[EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 <A target=_blank href="http://www.fatcity.com">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).

Received on Wed Jul 02 2003 - 16:26:33 CDT