Oracle gems: the LEVEL query

In one of my projects I was asked to build an APEX page with a calendar region. Normally that is pretty straightforward: in your query select at least a date and a description and you’re good to go.

But this time it was different. The table that I had to base my calender on did not contain a date column, but only weekdays. This table contains 5 entries for each employee, one for each workday of the week. Each row contains the number of hours the employee normally works and the number of parttime hours that the employee has for that day.
The table emp_calendar contains the following columns:

empno number
day_of_week number
work_hours number
parttime_hours number

The customer requested that the calendar would show all employees having a parttime day in a selected month. The problem was that because of the lack of a date column, it was difficult to plot the data to the calendar.

LEVEL to the rescue

Oracle contains a built-in feature called LEVEL. Using this in a query combined with the connect-by clause will create a loop inside the query.

For example:

select level
from dual
connect by level < 11

Will create a list from 1 to 10.

Using this feature, I could now create a query to retrieve the data for a full month.

select dd.level_date date_column
     , empno
  from emp_calendar
     , (SELECT (to_date(:P110_CALENDAR_DATE,'YYYYMMDD') + (LEVEL)) level_date
          FROM DUAL
       CONNECT BY LEVEL < 32) dd
 where day_of_week = to_char(dd.level_date,'D')
   and parttime_hours is not null

This is not the full query by the way, but an excerpt to show the most relevant pieces. Also it is implied that the CALENDAR_DATE is always the first of the month.

As you can see, the LEVEL query is done in a subquery. This is done for better performance.
The result of this is a calendar region looking like this: