RE: CASE in PL/SQL

[Surendra . Tirumala]

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 PM
To: Multiple recipients of list ORACLE-L
Subject: 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).