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., 220.127.116.11.0).
See the forum FAQ: Re: 2. How do I ask a question on the forums?
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, dates.examiner_id, dates.schedule_id FROM (SELECT -- bmv.schedule.common.data.AutoExaminerSchView schedule_id, start_date_time, end_date_time, examiner_id, exam_status 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;