Re: for each from/to date in table, select days between from/to date?

Thanks "Alien"!

Following advice found for someone else in this newsgroup, I also tried this, which works. It uses a "pipelined" function, which yiou can join to as if it were a table.

1. create an array of dates

CREATE OR REPLACE TYPE DATE_ARRAY AS TABLE OF DATE; 2. create a function that returns an array of dates between two dates. I got this off the web. Pipelined means it looks like a table when it comes back.

CREATE OR REPLACE FUNCTION DATES(FROMDATE DATE, TODATE DATE) RETURN DATE_ARRAY PIPELINED AS
BEGIN
  FOR i IN 0 .. (TODATE - FROMDATE)
  LOOP
    PIPE ROW(FROMDATE + i);
  END LOOP;
  RETURN;
END DATES; 3. Do the select. Use the TABLE keyword to turn the results as a table, then the NAMES table can join to it. Then just use BETWEEN in the WHERE.

SELECT N.NAME, N.COLOUR, D.COLUMN_VALUE DAY FROM NAMES N, TABLE(DATES(N.FROMDATE, N.TODATE)) WHERE D.COLUMN_VALUE BETWEEN N.FROMDATE AND N.TODATE; Cheers

Mark.

"Alien" <stijf_at_stijf.com> wrote in message news:1141053430.867098.82420_at_p10g2000cwp.googlegroups.com...
> Hi,
>
> How about this:
>
> SQL> ed
> file afiedt.buf is weggeschreven.
>
> 1 select a.name,a.colour,a.fd,a.td,a.fd+b.rn dt from
> 2 (select name,colour,fromdate fd,todate td, todate-fromdate nm
> 3 from names) a,
> 4 (select rownum-1 rn from dual connect by 1=1 and rownum<365) b
> 5 where b.rn<=a.nm
> 6* order by a.name,b.rn
> SQL> /
>
> NAME COLOUR
> FD TD DT
> --------------------------------------------------
> -------------------------------------------------
> Bob Red
> 07-01-06 09-01-06 07-01-06
> Bob Red
> 07-01-06 09-01-06 08-01-06
> Bob Red
> 07-01-06 09-01-06 09-01-06
> Fred Green
> 01-01-06 04-01-06 01-01-06
> Fred Green
> 01-01-06 04-01-06 02-01-06
> Fred Green
> 01-01-06 04-01-06 03-01-06
> Fred Green
> 01-01-06 04-01-06 04-01-06
>
> 7 rijen zijn geselecteerd.
>
> SQL>
>
> Regards,
>
> Arian
>
Received on Mon Feb 27 2006 - 10:01:04 CST