Java SE (Java Platform, Standard Edition)

NOT TESTED! http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns001.htm#sthref790

SELECT level - 1 + from_date "DAY", user_name
  FROM my_table
 WHERE connect_by_isleaf = 1
CONNECT BY LEVEL < (to_date - from_date + 1)
ORDER BY user_name;

Regards

Etbin

with my_table as (
                  select date '2009-10-15' from_date,date '2009-10-18' to_date,'thomas' user_name from dual union all
                  select date '2009-10-25',date '2009-10-30','jane' from dual
                 )
select  column_value,
        from_date + column_value - 1 day,
        user_name
  from  my_table,
        table(cast(multiset(select level from dual connect by from_date + level <=to_date) as sys.OdciNumberList))
  order by user_name,
           column_value
/

COLUMN_VALUE DAY       USER_N
------------ --------- ------
           1 25-OCT-09 jane
           2 26-OCT-09 jane
           3 27-OCT-09 jane
           4 28-OCT-09 jane
           5 29-OCT-09 jane
           1 15-OCT-09 thomas
           2 16-OCT-09 thomas
           3 17-OCT-09 thomas

8 rows selected.

SQL> 

SY.

Other solutions:

with my_table as (
                  select date '2009-10-15' from_date,date '2009-10-18' to_date,'thomas' user_name from dual union all
                  select date '2009-10-25',date '2009-10-30','jane' from dual
                 )
select  lvl,
        from_date + lvl - 1 day,
        user_name
  from  my_table,
        (select level lvl from dual connect by level <= (select max(to_date) - min(from_date) from my_table))
  where to_date >= from_date + lvl
  order by user_name,
           lvl
/

       LVL DAY       USER_N
---------- --------- ------
         1 25-OCT-09 jane
         2 26-OCT-09 jane
         3 27-OCT-09 jane
         4 28-OCT-09 jane
         5 29-OCT-09 jane
         1 15-OCT-09 thomas
         2 16-OCT-09 thomas
         3 17-OCT-09 thomas

8 rows selected.

SQL> 

with my_table as (
                  select date '2009-10-15' from_date,date '2009-10-18' to_date,'thomas' user_name from dual union all
                  select date '2009-10-25',date '2009-10-30','jane' from dual
                 )
select  level,
        from_date + level - 1 day,
        user_name
  from  my_table
  connect by user_name = prior user_name
         and from_date + level <=to_date
         and prior dbms_random.random is not null
  order by user_name,
           level
/

     LEVEL DAY       USER_N
---------- --------- ------
         1 25-OCT-09 jane
         2 26-OCT-09 jane
         3 27-OCT-09 jane
         4 28-OCT-09 jane
         5 29-OCT-09 jane
         1 15-OCT-09 thomas
         2 16-OCT-09 thomas
         3 17-OCT-09 thomas

8 rows selected.

SQL> 

Although DBMS_RANDOM behavior in hierarchical queries is version dependent.

SY.

Wrong, hoek, you should have read up to the part where OP stated:
Edited by: hoek on Nov 7, 2009 1:48 PM

And how is it different from DISTINCT? Just by doing GROUP BY for the same purpose?

SY.

Whoopsy!

Thanks, Solomon.
I should be drinking coffee during weekends as well, before replying on OTN ;)

Hi Hoek,
I think, you need rest :)

Regards salim.

I like model clause ;-)

with my_table as (
select date '2009-10-15' fromD,date '2009-10-18' toD,'thomas' user_name from dual
union all
select date '2009-10-25',date '2009-10-30','jane' from dual)
select fromD,user_name
  from my_table
 model
partition by(RowNum as rn)
dimension by(1 as soeji)
measures(fromD,toD,user_name)
rules(
fromD[for soeji from 1 to toD[1]-fromD[1] increment 1] = fromD[1]+cv(soeji)-1,
user_name[for soeji from 1 to toD[1]-fromD[1] increment 1] = user_name[1]);

FROMD     USER_N
--------  ------
09-10-15  thomas
09-10-16  thomas
09-10-17  thomas
09-10-25  jane
09-10-26  jane
09-10-27  jane
09-10-28  jane
09-10-29  jane

I like recursive with clause better than model clause B-)

Hahaha. I used postgreSQL8.4 :8}
I do not have Oracle11gR2 yet.

with recursive my_table(fromD,toD,user_name) as (
select date '2009-10-15',date '2009-10-18','thomas'
union all
select date '2009-10-25',date '2009-10-30','jane'),
W(fromD,toD,user_name) as(
select fromD,toD,user_name
  from my_table
union all
select (W.fromD+interval '1day')::date,W.toD,W.user_name
  from W,my_table b
 where W.user_name=b.user_name
   and (W.fromD+interval '1day')::date < W.toD)
select fromD,user_name from W
order by user_name,fromD;

   fromd    | user_name
------------+-----------
 2009-10-25 | jane
 2009-10-26 | jane
 2009-10-27 | jane
 2009-10-28 | jane
 2009-10-29 | jane
 2009-10-15 | thomas
 2009-10-16 | thomas
 2009-10-17 | thomas

OOPS there is more simple one.
LikeWise I used PostgreSQL8.4 :-)

with recursive my_table(fromD,toD,user_name) as (
select date '2009-10-15',date '2009-10-18','thomas'
union all
select date '2009-10-25',date '2009-10-30','jane'),
W(fromD,toD,user_name) as(
select fromD,toD,user_name
  from my_table
union all
select (W.fromD+interval '1day')::date,W.toD,W.user_name
  from W
 where (W.fromD+interval '1day')::date < W.toD)
select fromD,user_name from W
order by user_name,fromD;

   fromd    | user_name
------------+-----------
 2009-10-25 | jane
 2009-10-26 | jane
 2009-10-27 | jane
 2009-10-28 | jane
 2009-10-29 | jane
 2009-10-15 | thomas
 2009-10-16 | thomas
 2009-10-17 | thomas