Oracle-L: RE: CASE in PL/SQL

From: <Surendra.Tirumala_at_mail.state.ky.us>
Date: Thu, 03 Jul 2003 09:59:25 -0800
Message-ID: <F001.005C33AF.20030703095925@fatcity.com>

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 /

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.

-----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:Surendra.Tirumala_at_mail.state.ky.us] 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: 
  INET: Surendra.Tirumala_at_mail.state.ky.us

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: ListGuru_at_fatcity.com (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 Thu Jul 03 2003 - 12:59:25 CDT