help in query

[sailaja alle]

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

[Yogeeraj_1]

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

[sailaja alle]

Thank You Yoogerag,

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

[Yogeeraj_1]

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