Query to fetch all the months in a year using connect by (merged 3) [message #440884] |
Wed, 27 January 2010 13:48 |
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 (merged 3) [message #440891 is a reply to message #440884] |
Wed, 27 January 2010 15:23 |
|
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 |
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.
|
|
|
|
|