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.
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: