| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: CASE in PL/SQL
Hi Rudy,
Thanks for your suggestion. But I got a better suggestion from Metalink. Here what I was suggested:
SQL> SELECT (MOD(TO_NUMBER(TO_CHAR(add_months(sysdate,9),'Q'))+1,4) + 1) qtr
2 , DECODE(SIGN(TO_NUMBER(TO_CHAR(main_rec.termination_date,'Q')) - 3) 3 , -1, TO_NUMBER(TO_CHAR(main_rec.termination_date,'YYYY')) 4 , TO_NUMBER(TO_CHAR(main_rec.termination_date,'YYYY')) + 1) yr5 ...
Thanks for your help.
Surendra
-----Original Message-----
Sent: Wednesday, July 02, 2003 1:51 PM
To: Multiple recipients of list ORACLE-L
If your Jul is 1st quarter, then your offset is should be 6 months instead of the 9 months in your email; or think of it another way, if you Jan is the beginning of the 3rd quarter, it is the beginning of the 2nd half of the year, and half a year is 6 months.
With this in mind, you really don't need any decodes at all for just finding out your financial quarter. The following query suffices:
select to_char(add_months(sysdate,
6),
'Q') from user_users;
My best interpretation of your second query with the if-then is that if a date is in the second half of the calendar, you want to push it to the following year (or perhaps if the calendar date is from second half of last year, you want it reported as being in this year, which is kind of like saying fiscal year ending xxxx)
So, here's your solution (not exactly fast, but it's implemented competely with numeric functions provided by Oracle):
Let's make Q = to_number(to_char(add_months(sysdate, 6), 'Q')) To figure out how much correction you need to each year based on the quarter the year appears in, add the following to the year:
sign((sign(3 - Q) + 1) * sign(3 - Q))
-----Original Message-----
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 02, 2003 11:49 AM
To: Multiple recipients of list ORACLE-L
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: Rudy Zung 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).Received on Wed Jul 02 2003 - 14:14:19 CDT
 |
 |