how to stop connect by level by column values

[zxmgh]

Hi, I have to two tables, the tab_1 table has two date columns: start_time, end_time

create table tab_1 (col1 int, start_time date, end_time date);
create table tab_2 (col1 int, col2 int);

insert into tab_1 (col1, start_time, end_time) values (1, to_date('01/01/2014 05:00:00', 'mm/dd/yyyy hh24:mi:ss'), to_date('01/01/2014 08:00:00', 'mm/dd/yyyy hh24:mi:ss'));

insert into tab_2 (col1, col2) values (10, 1);
insert into tab_2 (col1, col2) values (11, 1);

I would like to return rows that starting from start_time increase by one hour each till it reaches end_time, like this:


1 10 1, 01/01/2014 05:00:00
1 10 2, 01/01/2014 06:00:00
1 10 3, 01/01/2014 07:00:00
1 10 4, 01/01/2014 08:00:00
1 11 1, 01/01/2014 05:00:00
1 11 2, 01/01/2014 06:00:00
1 11 3, 01/01/2014 07:00:00
1 11 4, 01/01/2014 08:00:00

The following query is not working, please help

select d.col1, s.col1, rownum, to_char(trunc(d.start_time, 'HH24') + level/24, 'mm/dd/yyyy hh24:mi:ss')
from tab_1 d, tab_2 s
where d.col1 = s.col2
connect by level <= (trunc(d.end_time, 'HH24') - trunc(d.start_time, 'HH24'))*24;

[LKBrwn_DBA]

Please learn to format your code using the "code" tabs (select your code and click on the hash icon above).

Try this:

  SELECT D.Col1
       , S.Col1
       , ROW_NUMBER ( ) OVER ( PARTITION BY D.Col1, S.Col1 ORDER BY H.Lvl0 ) Row_Num
       , TO_CHAR ( TRUNC ( D.Start_Time, 'HH24' ) + ( H.Lvl0 - 1 ) / 24, 'mm/dd/yyyy hh24:mi:ss' )  The_Hour
    FROM Tab_1 D
       , Tab_2 S
       , ( SELECT LEVEL Lvl0 FROM DUAL
                 CONNECT BY LEVEL <=( 
                        SELECT ( MAX ( D.End_Time ) - MIN ( D.Start_Time ) ) * 24 + 1
                          FROM Tab_1 D ) ) H
   WHERE D.Col1 = S.Col2
/