Advice on calendar function

From: <artmt_at_hotmail.com>
Date: 28 Aug 2006 10:06:41 -0700
Message-ID: <1156784801.685662.314210@i42g2000cwa.googlegroups.com>

I am considering writing a table function to return a row for each calendar date within supplied data range.

Here is the SQL:

SELECT to_date(day_year_seq||'-'||year,'ddd-yyyy'),day_abs_seq FROM(SELECT day_abs_seq,

            year,
            row_number() over(partition by year order by day_abs_seq)
day_year_seq
     FROM (SELECT rownum day_abs_seq
	   FROM dba_objects
           WHERE rownum <= :dt_end-trunc(:dt_start,'year')+1) a,
          (SELECT year,
                  to_date('01-jan-'||year)-trunc(:dt_start,'year')+1
day_start_abs_seq,
                  to_date('31-dec-'||year)-trunc(:dt_start,'year')+1
day_end_abs_seq
           FROM(SELECT rownum+to_number(to_char(:dt_start,'yyyy'))-1
year
                FROM dba_objects
                WHERE rownum <=

to_number(to_char(:dt_end,'yyyy'))-to_number(to_char(:dt_start,'yyyy'))+1)) b

    WHERE a.day_abs_seq between b.day_start_abs_seq and b.day_end_abs_seq);

Any comments of the approach?
In particular are there advantages to using the dictionary vs non-dictionary row source?
Is dba_objects a good choice?
Any other toughts?

Thanks
Art Received on Mon Aug 28 2006 - 12:06:41 CDT