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"
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









4 comments:

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' ?

Patrick

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.