Create Virtual Tables

This is one of frequently asked questions:

"I have only a few lines of data - mostly some dates with a duration from > to - and would like to use this as table to select from and populate a calendar. This calendar is supposed to show one entry for each day within a time period. How do I do that? Do I need to create a separate table?"

The answer is "No, you don't.". Using CONNECT BY LEVEL, you can create virtual tables and use those in your select statements. For example: there was a question in this post, how to show all Fridays within a certain period of time. Depending on your NLS settings you would do something like this to create a virtual table, showing all Fridays from the beginning of the year to the current day:

SELECT each_day "friday"
  FROM (SELECT       (  TRUNC (SYSDATE)
                      - TO_NUMBER (TO_CHAR (TO_DATE (SYSDATE), 'ddd'))
                     )
                   + LEVEL each_day
              FROM DUAL
        CONNECT BY LEVEL <= TO_NUMBER (TO_CHAR (TO_DATE (SYSDATE), 'ddd')))
 WHERE TO_CHAR (each_day, 'D') = '5'

Depending on your NLS settings, a Friday could be the fifth or the sixth day of the week.

In my demo application, you will find an interesting example on how to populate a calender for three events (three records) stored in a table, getting for each day within a given period of time, one entry in a calendar:

http://htmldb.oracle.com/pls/otn/f?p=31517:83