RE: CASE in PL/SQL

[Surendra . Tirumala]

Title: RE: CASE in PL/SQL

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.

---

From: Surendra Tirumala 02-Jul-03 19:44
Subject: Re : CASE in PL/SQL

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

---

From: T Tebbe 02-Jul-03 20:27
Subject: Re : CASE in PL/SQL

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-----
From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 03, 2003 9:11 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: CASE in PL/SQL

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-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 02, 2003 6:11 PM
To: Multiple recipients of list ORACLE-L
Subject: 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