Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Advice on calendar function
<artmt_at_hotmail.com> a écrit dans le message de news: 1156784801.685662.314210_at_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
Received on Mon Aug 28 2006 - 12:42:41 CDT