topic help in query in Operating System - HP-UX

help in query

sailaja alle — Mon, 10 Mar 2008 04:37:37 GMT

can i get values in result set as weeknum alone from 1 to 53 using all_objects.

i wrote a query but it is returning only one week number. i will paste it over here.

but i want it to be done without using level and connect by....

select to_char(trunc(sysdate,'yyyy')+(rownum-1),'ww-yyyy') weeks
from all_objects
where rownum <=366
and to_char(trunc(sysdate,'yyyy')+rownum-1 ,'dd') = 1

Please rectify the mistake in the query... ASAP

Re: help in query

Yogeeraj_1 — Mon, 10 Mar 2008 05:38:24 GMT

hi Sailaja,

can you try the following:

yogeeraj@mydb.mu> r
1 select distinct to_char( dt+1, 'iw' )
2 from ( select trunc(sysdate,'YY')+rownum dt
3 from all_objects
4 where rownum < 366 )
5* order by 1

TO
__
01
02
03
04
05
06
07
08
...
49
50
51
52

52 rows selected.

Elapsed: 00:00:00.27
yogeeraj@mydb.mu>
hope this helps!

kind regards
yogeeraj

Re: help in query

sailaja alle — Mon, 10 Mar 2008 05:53:13 GMT

Thank You Yoogerag,

But can i get some thing without using "distinct".

Re: help in query

Yogeeraj_1 — Mon, 10 Mar 2008 06:24:49 GMT

hi,

Once 'dirty' way to achieve this is:

yogeeraj@mydb.mu> r
1 select to_char( dt+1, 'iw' )
2 from ( select trunc(sysdate,'YY')+rownum*7 dt
3 from all_objects
4 where rownum < 53 )
5* order by 1

TO
__
01
02
03
04
05
06
07
08
...
49
50
51
52

52 rows selected.

Elapsed: 00:00:00.12
yogeeraj@mydb.mu>

hope this helps!

kind regards
yogeeraj