From jayadas.chelur@pepsi.com Mon, 07 Jul 2003 09:15:51 -0700 From: "Chelur, Jayadas {PBSG}" Date: Mon, 07 Jul 2003 09:15:51 -0700 Subject: RE: CASE in PL/SQL Message-ID: MIME-Version: 1.0 Content-Type: text/plain The problem was explained clearly but the context in which it had to be used was obscure. Rajendra Jamadagni had suggested a solution using functions which could be called from SQL and you had mentioned that it could NOT be used in a CURSOR (!). The 'simplest' way to calculate the financial quarter is to offset any given date by 6 months and then find the regular quarter using 'Q' format in TO_CHAR(). Rudy Zung had explained this very clearly in one of the earlier postings and I had also suggested this and using an inline view to find the offset from the actual calendar year. -----Original Message----- [mailto:[EMAIL PROTECTED] Sent: Thursday, July 03, 2003 1:59 PM To: Multiple recipients of list ORACLE-L I think I have clearly explained what I am doing/looking for in my original mail. As I have mentioned in one of the replies, I have posted same question in Metalink Forum and they gave me the exact/simple solution I am looking for. Here is below the conversation between me and OSS person. Anyway thank you all for your replies. Surendra ================ Surendra, You could code: 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) yr 5 ... 6 Here's a data sampling of the above: DT QTR YR -------------- ---------- ---------- Wed 1/9/2002 3 2002 Wed 3/6/2002 3 2002 Sat 3/30/2002 3 2002 Sun 4/7/2002 4 2002 Fri 5/17/2002 4 2002 Wed 6/26/2002 4 2002 Thu 7/4/2002 1 2003 Mon 9/30/2002 1 2003 Tue 10/8/2002 2 2003 Fri 12/27/2002 2 2003 Sat 1/4/2003 3 2003 Wed 2/5/2003 3 2003 Tue 3/25/2003 3 2003 Wed 4/2/2003 4 2003 Tue 5/20/2003 4 2003 Sun 6/29/2003 4 2003 Mon 7/7/2003 1 2004 Thu 9/25/2003 1 2004 Fri 10/3/2003 2 2004 Sat 10/11/2003 2 2004 Tue 12/30/2003 2 2004 Wed 1/7/2004 3 2004 Wed 3/3/2004 3 2004 Thu 3/11/2004 3 2004 HTH, T. _____ 02-Jul-03 19:44 Hi Tebbe, "SELECT (MOD(TO_NUMBER(TO_CHAR(add_months(sysdate,9),'Q'))+1,4) + 1) qtr , DECODE(SIGN(TO_NUMBER(TO_CHAR(sysdate,'Q')) - 3) , -1, TO_NUMBER(TO_CHAR(sysdate,'YYYY')) , TO_NUMBER(TO_CHAR(sysdate,'YYYY')) + 1) yr from dual " This query is what I was looking for . Thankyou very much. But I did not understand why you are adding '1' to to_number(TO_CHAR(add_months(sysdate,9),'Q'))? Thanks again Surendra _____ Good question, Surendra. To answer it, I'll be beginning with the following query, which does nothing more than returns eight dates for us to work with. These eight dates fall in eight different quarter/year combinations, so it'll be a good test: SQL> SELECT ADD_MONTHS(TO_DATE('20030101','YYYYMMDD'),(ROWNUM - 1) * 3) dt 2 FROM sys.all_users 3 WHERE ROWNUM <= 8 4 / DT ----------- 01-JAN-2003 01-APR-2003 01-JUL-2003 01-OCT-2003 01-JAN-2004 01-APR-2004 01-JUL-2004 01-OCT-2004 8 rows selected. Now, we use TO_CHAR to convert these dates to quarters the way Oracle has defined them: SQL> SELECT dt 2 , TO_CHAR(dt,'Q') as_q 3 FROM (SELECT ADD_MONTHS(TO_DATE('20030101','YYYYMMDD'),(ROWNUM - 1) * 3) dt 4 FROM sys.all_users 5 WHERE ROWNUM <= 8) 6 / DT A ----------- - 01-JAN-2003 1 01-APR-2003 2 01-JUL-2003 3 01-OCT-2003 4 01-JAN-2004 1 01-APR-2004 2 01-JUL-2004 3 01-OCT-2004 4 8 rows selected. I'm going to perform math on those quarters, but right now they're characters (TO_CHAR), so I have to do a TO_NUMBER on that column: SQL> SELECT dt 2 , TO_NUMBER(TO_CHAR(dt,'Q')) as_q 3 FROM (SELECT ADD_MONTHS(TO_DATE('20030101','YYYYMMDD'),(ROWNUM - 1) * 3) dt 4 FROM sys.all_users 5 WHERE ROWNUM <= 8) 6 / DT AS_Q ----------- ---------- 01-JAN-2003 1 01-APR-2003 2 01-JUL-2003 3 01-OCT-2003 4 01-JAN-2004 1 01-APR-2004 2 01-JUL-2004 3 01-OCT-2004 4 8 rows selected. Now, I want to turn Oracle's quarters (1,2,3,4,1,2,3,4) into your quarters (3,4,1,2,3,4,1,2). I can't just add 2, because then for quarters 3 and 4 I'd get quarters 5 and 6, which makes no sense. So I use MOD. I need to use MOD(something,4) because there are four quarters to a year. MOD(positive integer,4) will always return one of 0,1,2 or 3, so the OUTERMOST "+ 1" converts the members of this 0,1,2 and 3 subset (which we don't want) into 1,2,3 and 4 (which we do want). So to get from 1,2,3,4,1,2,3,4 (what we're starting with) to 2,3,0,1,2,3,0,1 (where we want to be), I add one (that's the innermost "+1" you're asking about). SQL> SELECT dt 2 , TO_NUMBER(TO_CHAR(dt,'Q')) as_q 3 , TO_NUMBER(TO_CHAR(dt,'Q')) + 1 "AS_Q + 1" 4 , MOD(TO_NUMBER(TO_CHAR(dt,'Q')) + 1,4) "MOD_4'D" 5 , MOD(TO_NUMBER(TO_CHAR(dt,'Q')) + 1,4) + 1 "MOD_4'D + 1" 6 FROM (SELECT ADD_MONTHS(TO_DATE('20030101','YYYYMMDD'),(ROWNUM - 1) * 3) dt 7 FROM sys.all_users 8 WHERE ROWNUM <= 8) 9 / DT AS_Q AS_Q + 1 MOD_4'D MOD_4'D + 1 ----------- ---------- ---------- ---------- ----------- 01-JAN-2003 1 2 2 3 01-APR-2003 2 3 3 4 01-JUL-2003 3 4 0 1 01-OCT-2003 4 5 1 2 01-JAN-2004 1 2 2 3 01-APR-2004 2 3 3 4 01-JUL-2004 3 4 0 1 01-OCT-2004 4 5 1 2 8 rows selected. SQL> The last column there follows your organization's fiscal quarter schedule. Hope this helps, Surendra. T. -----Original Message----- Sent: Thursday, July 03, 2003 9:11 AM To: Multiple recipients of list ORACLE-L Can you at-least _show_ us what you are doing, what you want to do and where the code is failing? We are shooting in the dark here .. Like I mentioned before, you can _always_ use SQL to assign values to pl/sql variables. 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----- [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 6:11 PM To: Multiple recipients of list ORACLE-L I am already using it that way, but giving that condition in cursor is not possible. Thanks for your help. Surendra -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chelur, Jayadas {PBSG} 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).