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









on

4 comments:

  1. Anonymous16 May 2007 at 01:59

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

    ReplyDelete
  2. Patrick Wolf16 May 2007 at 08:24

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

    Patrick

    ReplyDelete
  3. Denes Kubicek16 May 2007 at 09:59

    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

    ReplyDelete
  4. Jornica18 May 2007 at 20:34

    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.

    ReplyDelete