<artmt@hotmail.com> a écrit dans le message de news: 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
|

SQL> with dates as (select sysdate-7 dt_start, sysdate+7 dt_end from dual)
2 select dt_start+rownum-1 "Date"
3 from dates
4 connect by level <= dt_end-dt_start
5 /
Date
----------
08/21/2006
08/22/2006
08/23/2006
08/24/2006
08/25/2006
08/26/2006
08/27/2006
08/28/2006
08/29/2006
08/30/2006
08/31/2006
09/01/2006
09/02/2006
09/03/2006

14 rows selected.

Regards
Michel Cadot