Home » SQL & PL/SQL » SQL & PL/SQL » Query to fetch all the months in a year using connect by (merged 3) (Oracle 9.2)
Query to fetch all the months in a year using connect by (merged 3) [message #440884] Wed, 27 January 2010 13:48 Go to next message
sqlstar_student
Messages: 42
Registered: November 2007
Member
Oracle 9.2

Query or a procedure which will output the list of all the months in a year.

Ex:


create package pack
is
type cal is varray(12) of varchar2(20); -- we can capture the output either in varray or through ref cursor;
type p1 is ref cursor return varchar2;
type rec is RECORD(ddate date);
end pack;

create procedure list(ddate date, p1 out pack.p1)
as

begin
open p1 for select month(to_char(ddate,'dd-mon-yyyy'))
from dual
connect by prior month.... unable to figure out this part? how to fetch each month from the year.;
end list;

declare
refcur pack.p1;
v_rec pack.rec;
begin
list(22-10-2009,refcur);
loop
exit when refcur%notfound
dbms_output.put_line(refcur||' '||);
end loop;
end;

will this work.
Re: Query to fetch all the months in a year using connect by [message #440887 is a reply to message #440884] Wed, 27 January 2010 13:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>will this work.
I don't think so.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Query to fetch all the months in a year using connect by (merged 3) [message #440891 is a reply to message #440884] Wed, 27 January 2010 15:23 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Your approach is overkill; what you need is a simple query which uses CONNECT BY. Basically, it is a row generator technique - you need to produce 12 rows:
SQL> select level
  2  from dual
  3  connect by level <= 12;

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12

12 rows selected.

SQL>

Now, the problem is how to display those months using words (if that's what you need). One way is to add output of the previous query to the SYSDATE truncated to YYYY (i.e. 1st of January):
SQL> select trunc(sysdate, 'yyyy') from dual;

TRUNC(SYSD
----------
01.01.2010

SQL>
"Adding" should be done in months. Oracle function capable of doing that is ADD_MONTHS. For example:
SQL> select add_months(trunc(sysdate, 'yyyy'), 5) from dual;

ADD_MONTHS
----------
01.06.2010

SQL>
The final step is easy: TO_CHAR will convert date into month:
SQL> select to_char(sysdate, 'month') From dual;

TO_CHAR(SYSDATE,'MONTH')
------------------------------------
january

SQL>

I guess that's it; now - your turn. Put all those pieces together and you'll have it. 3 lines exactly. SELECT / FROM / CONNECT BY.


Ummm, right, you're on 9i. I *think* CONNECT BY won't work that way - you'll have to wrap it; something like this:
SQL> select lvl
  2  from (select level lvl
  3        from dual
  4        connect by level <= 12
  5       );

       LVL
----------
         1
         2
         3
etc.
Unfortunately, I don't have 9i here to prove it. The above example was created on 10g XE.

[Updated on: Wed, 27 January 2010 15:26]

Report message to a moderator

Re: Query to fetch all the months in a year using connect by (merged 3) [message #440893 is a reply to message #440891] Wed, 27 January 2010 16:14 Go to previous messageGo to next message
sqlstar_student
Messages: 42
Registered: November 2007
Member
Thanks a lot, littlefoot. Thats a really good example, however help me understand queries with connect by, or we can say hierarchial queries. Please.

However i am unable to understand how connect by is accomplishing this task, or any other task for that matter for example say


create table emp(empno number, ename varchar2(20), mgr number);

insert into emp values(7788, SCOTT, 7566);
insert into emp values(7876, ADAMS, 7788);
insert into emp values(7902, FORD, 7566);
insert into emp values(7369, SMITH, 7902);

select empno,ename, mgr
from emp
connect by prior empno=mgr

here this query will fetch all the employees whose employee no is the mgr no of the next record.

so the prior row empno is the manager of the next employee so on.

EMPNO ENAME MGR
---------- ---------- ----------
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7499 ALLEN 7698

Am i right in understanding so far.

select level
2 from dual
3 connect by level <= 12;

when u say select level, level is a pseudo column and i dont understand what connect by level <=12 means?

Regards.
Re: Query to fetch all the months in a year using connect by (merged 3) [message #440897 is a reply to message #440893] Wed, 27 January 2010 18:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
We only know what you post & get annoyed when folks mis-state reality.

Based upon what you lasted posted your data & SQL could NOT have returned the following record


7499 ALLEN 7698


which never got inserted into your table & has no manager in the table anyways

Once again, it would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

such as using sqlplus along with CUT & PASTE so we can see what you actually do & how Oracle REALLY responds.

[Updated on: Wed, 27 January 2010 18:55]

Report message to a moderator

Re: Query to fetch all the months in a year using connect by (merged 3) [message #440898 is a reply to message #440893] Wed, 27 January 2010 19:22 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Although the row generator query is very useful - it's far from intuative and it's probably best to just treat it as a black box. It didn't even produce consistent results in different environments when it was originally invented/discovered.

Yes, 'level' is a pseduo column that applies to 'connect by' queries.

-- and early ref to the technique...
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12718072439781#34043422076040


Previous Topic: SQL QUERY TO ROLLOVER PRIOR YEAR VALUES TO CURRENT YEAR
Next Topic: Null values top by ascending order
Goto Forum:
  


Current Time: Mon Nov 04 11:58:27 CST 2024