Select statement that return all the month days

[Lowaiy]

Dear Sir,

How can I write Select statement that return all the days in the current month or year... For example the results can be like:

1-1-05
2-1-05
3-1-05
4-1-05
...
...
...

Thank you in advance.

[DaPi]

select trunc(sysdate,'MON')+rownum-1 from all_objects where rownum <= 28

See here: http://www.dbasupport.com/forums/sho...threadid=44529 for other methods of generating rows.

[slimdave]

Maybe generalise that with ...

select trunc(sysdate,'MON')+rownum-1
from   all_objects
where  rownum <= Add_Months(trunc(sysdate,'MON'),1)-trunc(sysdate,'MON')

btw, the all_objects view performs dismally on my 10g system compared to dba_objects or user_objects -- tsh.

I favour a nice big table of integers for this sort of thing.

[DaPi]

Originally posted by slimdave
Maybe generalise that with ...

Sure, but I like to leave the original poster with something to do . . .

[Lowaiy]

Thank you sirs for your fast help... Anyhow maybe we can although use the following statement for a year:

select to_char(trunc(sysdate,'YY') - 1 + rownum, 'dd-mm-yy') from
(select rownum from all_catalog where rownum < 366);

Regards,

[slimdave]

Why are you using to_char(), and why use a two-digit year picture? How about making it properly responsive to leap years?

[Lowaiy]

Yes sir, definitely you're right. When we need a statement like this, absolutely we need it fully dynamic.
But I need you opinion if we want to use this statement in generating dynamic reports. I mean that I want to join this statement with another table which has column (DAY_NO) and it's values is enumerators: (1,2,3,4,5,6,7) (i.e. Monday is 1, Tuesday is 2 etc...).
So the join will be by getting the current statement day of the week and comparing it with the table DAY_NO column -The statement could be like this-:
********************************************************************
SELECT trunc(sysdate,'MON')+rownum-1,
shf.time_in,
shf.time_out,
shf.day_no,
FROM
all_objects aobj,
shifts shf
WHERE
day_no = to_char(trunc(sysdate,'YY') - 1 + rownum, 'D')
and
rownum <= Add_Months(trunc(sysdate,'MON'),1)-trunc(sysdate,'MON')
*********************************************************************

Do think that if put the values of the statement which return the days of month in a table is better than using it every time?
And do you have any suggestions in order to make tuning to the previous example of SQL statement (maybe materialized view or something like this...)?

Thank you in advance.

[DaPi]

I suspect that will be very slow (and maybe blow your temp TS) - you are making a Cartesian product of all_objects and your table. The optimizer might be clever . . . . but if not, try something like:

SELECT my_date, 
shf.time_in,
shf.time_out,
shf.day_no,
FROM
shifts shf,
(select trunc(sysdate,'MON')+rownum-1 my_date
 from   user_objects
 where  rownum <= Add_Months(trunc(sysdate,'MON'),1)-trunc(sysdate,'MON'))
WHERE
day_no = to_char(my_date, 'D')

I think it is also more understandable.

[slimdave]

With the "rownum <= ..." clause in the in-line view, is that sufficient to prevent view merging in any circumstances you think? I'm guessing that it would be, but thoughts are welcome of course.

[DaPi]

Weeeellllll . . . . the attempt by Lowaiy with a rownum condition on the "join" of the two tables is definitely not what is wanted - it won't give the right answer (as far I understand the question). I don't see how you can write it correctly like that, because you can't refer to alias.rownum

I've just tried:

select count(*)
from   my_table r,
(select trunc(sysdate,'MON')+rownum-1 my_date
 from   user_objects
 where  rownum <= Add_Months(trunc(sysdate,'MON'),1)-trunc(sysdate,'MON'))
where  mod(myTablePK,10) = to_char(my_date, 'D')

With tables from 20 to 2'000'000 rows. They all work as I'd like/expect - the only difference for the 20-row table being which set is the driver in the hash join.