Tuesday, 15 May 2007

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"
+ LEVEL each_day
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:



Anonymous said...

Hope you are not using this in a production environment. If so, use it at your own risk.

Patrick Wolf said...

Why not using CONNECT BY in a production environment? Or are you talking about the TO_CHAR(each_day, 'D') = '5' ?


Denes Kubicek said...

anonymus, why not? About

TO_CHAR(each_day, 'D') = '5'

the fifth day of the week in Europe is Friday. In the US it would be

TO_CHAR(each_day, 'D') = '6'

An explanation and a name would be good.

Denes Kubicek

Jornica said...

There is a third parameter for the to_char function: NLS_DATE_LANGUAGE. For example,
TO_CHAR(TO_DATE('18052007','DDMMYYYY'), 'day','NLS_DATE_LANGUAGE=german') returns freitag.
By using datetime format element 'day' instead of 'd' we are also bypassing the setting of NLS_TERRITORY which specifies the first day of the week. As a result the SQL is not dependent of an 'hidden' NLS setting (not included in the statement) and easier to read as well.