Re: Advice on calendar function


<artmt@xxxxxxxxxxx> a écrit dans le message de news: 1156784801.685662.314210@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
|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


.

Relevant Pages

  • Advice on calendar function
    ... calendar date within supplied data range. ... Here is the SQL: ... FROM (SELECT rownum day_abs_seq ...
    (comp.databases.oracle.server)
  • Re: Calendar Form
    ... If it's the name of a query, I need to see the SQL of that query. ... My Calendar form opens, and I can add information into it, as well as ... The fact that the previous line (Dim db As DAO.Database) doesn't ...
    (microsoft.public.access.forms)
  • Re: How to understand huge code
    ... product) I feel very difficult understanding the complete code flow. ... writing it in a semi-programming language helps reduce clutter. ... For things like SQL, I ask myself what the SQL is retrieving ...
    (comp.programming)
  • Re: Is there a way to keep the StreamWriter open?
    ... SQL that I can writing to a text file with some decient programming ... hardware and it would be able to keep up but not be faster. ... It is, in some ways, but the process writing the IIS logs is optimized ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: NEED TO GO FASTER
    ... Writing your entire message in UPPER CASE is considered shouting, ... entirely written in upper case, then it is very had to read (especially ... SQL is a set oriented language, ... Make sure the proper indexes are in place. ...
    (comp.databases.ms-sqlserver)
Loading