Select statement that return all the month days

[slimdave]

Originally posted by DaPi
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.

Yes, I think that "rownum< very_large_number" would be the same as "rownum>=1" in this respect, because once you joined the data set to another set (particularly with a cartesian product) there is no way of identifying which of the user_objects rows would satisfy the rownum predicate. Hadn't thought about that before.

[tamilselvan]

Avoid using user_objects...
Try this:

In 9i
SQL> select my_date
  2  from
  3  (select /*+ NO_MERGE */
  4          trunc(sysdate,'MON')+rownum-1 my_date
  5   from   dual
  6   connect by 1=1
  7   and   rownum <= Add_Months(trunc(sysdate,'MON'),1)-trunc(sysdate,'MON'));

MY_DATE
---------
01_FEB-05
02_FEB-05
03_FEB-05
04_FEB-05
05_FEB-05
06_FEB-05
07_FEB-05
08_FEB-05
09_FEB-05
10_FEB-05
....
...
24_FEB-05
25_FEB-05
26_FEB-05
27_FEB-05
28_FEB-05

28 rows selected.

Elapsed: 00:00:00.01

In 10g
SQL> L
  1  select /*+ NO_MERGE */
  2          trunc(sysdate,'MON')+rownum-1 my_date
  3   from   dual
  4   connect by 1=1
  5*  and   rownum <= (Add_Months(trunc(sysdate,'MON'),1)-trunc(sysdate,'MON'))-1
SQL> /

MY_DATE
-------------------
02/01/2005 00:00:00
02/02/2005 00:00:00
02/03/2005 00:00:00
02/04/2005 00:00:00
02/05/2005 00:00:00
02/06/2005 00:00:00
02/07/2005 00:00:00
02/08/2005 00:00:00
02/09/2005 00:00:00
02/10/2005 00:00:00
...
...
MY_DATE
-------------------
02/23/2005 00:00:00
02/24/2005 00:00:00
02/25/2005 00:00:00
02/26/2005 00:00:00
02/27/2005 00:00:00
02/28/2005 00:00:00

28 rows selected. 

Tamil

[DaPi]

Hi Tamil,

I'd seen the trick with hierachical query, but it only returns one row in 8i. It's a bit nasty if you have to code it two different ways for 9i & 10g.

I gave the link to our previous discussion where padders did some timings on various methods. The method of a function that returns a table in memory looked nice to me - I feel that the integers, being abstract objects, live in memory rather than on disk

[slimdave]

Originally posted by DaPi
I feel that the integers, being abstract objects, live in memory rather than on disk

I usually go more for methods that are intuitively understood as long as they perform OK, like having a UTL_INTEGERS table with a big list of integers, or a UTL_DATES table with a big list of dates. I think the code benefits from reduced funkiness.

[DaPi]

Originally posted by slimdave
I usually go more for methods that are intuitively understood as long as they perform OK, like having a UTL_INTEGERS table with a big list of integers, or a UTL_DATES table with a big list of dates.

Hmmmmm . . . . .

And which end do you open a boiled egg? EH?

This could mean WAR