Re: Using SELECT to fill in missing entries?

1.)

You will create a function which will return an array of dates, e.g. (in 9i you can create a pipelined function):

CREATE TYPE date_array AS TABLE OF DATE;

CREATE OR REPLACE FUNCTION Date_Table( p_start_date DATE,p_end_date DATE )
  RETURN date_array
    PIPELINED
 AS

    BEGIN
    FOR i IN 0 .. (p_end_date-p_start_date) LOOP

       pipe ROW (p_start_date+i);
    END LOOP;
    RETURN;
 END Date_Table;
/

2.) I wil create some demo table with 2 rows:

CREATE TABLE T (DT DATE); INSERT INTO T VALUES (TRUNC(SYSDATE-10)); INSERT INTO T VALUES (TRUNC(SYSDATE)); 3.)

And then, I will make outer join between them:

SELECT * FROM
  TABLE(CAST (date_table(TRUNC(SYSDATE)-10,TRUNC(SYSDATE)) AS date_ARRAY)) vt,
  T
 WHERE T.dt(+)=vt.COLUMN_VALUE

Jan

laredotornado_at_gmail.com (D. Alvarado) wrote in message news:<ec027e73.0409040824.78cfbbda_at_posting.google.com>...
> Hello, I am running Oracle 8.1.7 on Solaris 8. I have a table that
> records the number of registrants per day. Here is an example of some
> data.
>
> DAY NUM_REGISTRANTS
> --- ---------------
> 2004-07-01 2
> 2004-07-04 5
> 2004-07-06 3
>
> When a day is not present, it means there were 0 registrants on that
> day. Does anyone know, given a beginning and ending date, how to form
> a SELECT statement such that I can select entries for days even if no
> entries are in the table? That is, given the dates 2004-07-01 and
> 2004-07-06, how would I form a SELECT statement that would produce the
> output ...
>
> DAY NUM_REGISTRANTS
> --- ---------------
> 2004-07-01 2
> 2004-07-02 0
> 2004-07-03 0
> 2004-07-04 5
> 2004-07-05 0
> 2004-07-06 3
>
> ? Thanks for your help, - Dave
Received on Mon Sep 06 2004 - 06:35:21 CDT