2 Replies Latest reply: Sep 4, 2014 9:29 AM by Boneist RSS

    CONNECT BY LEVEL with dates


      I am trying to select available time slots (hours) between two date columns.

      I'm using CONNECT BY LEVEL with a non-equal operator:


            (dates.start_date_time + (level - 1) /24) start_date_time,



      FROM (SELECT -- bmv.schedule.common.data.AutoExaminerSchView 






            FROM schedule_test

            WHERE start_date_time >= TO_DATE('1/6/2014 7','MM/DD/YYYY HH24')  -- during the

          AND end_date_time <= TO_DATE('1/10/2014 15','MM/DD/YYYY HH24')  -- scheduling week

          AND exam_status = 'AVAILABLE'                               -- with available schedule

          AND ROWNUM < 2) dates

      WHERE EXTRACT(HOUR FROM NUMTODSINTERVAL(   -- before the end hour (4 pm)

        (dates.start_date_time + (level - 1) /24) - TRUNC(

        (dates.start_date_time + (level - 1) /24)), 'DAY')) < TO_NUMBER(TO_CHAR(dates.end_date_time, 'HH24'))

      CONNECT BY dates.start_date_time + (level - 1) /24 <= dates.end_date_time -- each hour between dates

      ORDER BY (dates.start_date_time + (level - 1) /24),dates.examiner_id,dates.schedule_id;


      The docs say:

      Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, this can result in an infinite loop through the possible combinations.

      My strategy works well for one record, but when there are more than one record the results become exponentially large.


      The attached SQL+ script sets up some test data and runs two sample queries.

      Could someone demonstrate the correct way to select these data?

      Any pointers and help are appreciated.

        • 1. Re: CONNECT BY LEVEL with dates
          Frank Kulash



          If you use  CONNECT BY x <= y  , then at least one of the following should be true:

          • There is exactly 1 row in the table
          • You have other conditions, using the PRIOR operator, in the CONNECT BY clause


          Try something like this:

          CONNECT BY   start_date_time + ((level - 1) /24)  <= end_date_time      -- each hour between dates

                  AND  PRIOR schedule_id                    = schedule_id         -- or anything that is unique

                  AND  PRIOR SYS_GUID ()                    IS NOT NULL

          I hope this answers your question.

          If not, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.  Post it right in this site.

          Explain, using specific examples, how you get those results from that data.

          Always say which version of Oracle you're using (e.g.,

          See the forum FAQ: Re: 2. How do I ask a question on the forums?

          • 2. Re: CONNECT BY LEVEL with dates

            You just need to add some conditions in the connect by.

                       and prior schedule_id = schedule_id
                       and prior dbms_random.value is not null


            Btw, I don't think you need the where clause; you ought to be able to amend the time period in the connect by to restrict the rows as required. You might need to add trunc(<date>, 'hh') as necessary (maybe in the select list too, if your start/end times aren't always on the hour but you need your report to be on the hour).


            Maybe something like:


            SELECT     (dates.start_date_time + (LEVEL - 1) / 24) start_date_time,
            FROM       (SELECT               -- bmv.schedule.common.data.AutoExaminerSchView
                        FROM   schedule_test
                        WHERE      start_date_time >= TO_DATE ('1/6/2014 7', 'MM/DD/YYYY HH24') -- during the
                               AND end_date_time <= TO_DATE ('1/10/2014 15', 'MM/DD/YYYY HH24') -- scheduling week
                               AND exam_status = 'AVAILABLE'      -- with available schedule
                               ) dates
            CONNECT BY dates.start_date_time + LEVEL / 24 <= dates.end_date_time -- each hour between dates
                       and prior schedule_id = schedule_id
                       and prior dbms_random.value is not null
            ORDER BY   dates.start_date_time + LEVEL / 24,
                       dates.examiner_id, dates.schedule_id;