Hi

I have a 'NAMES' table:

NAME COLOUR FROMDATE TODATE
Fred Green 01-JAN-2006 04-JAN-2006
Bob Red 07-JAN-2006 09-JAN-2006
Alf Blue 12-JAN-2006 13-JAN-2006

In one query, for each row, I want to return a row for each day between the
FROMDATE and the TODATE, inclusive.

So, for the first row, you'd get:

NAME COLOUR DAY
Fred Green 01-JAN-2006
Fred Green 02-JAN-2006
Fred Green 03-JAN-2006
Fred Green 04-JAN-2006

etc. So overall, you'd get:

NAME COLOUR DAY
Fred Green 01-JAN-2006
Fred Green 02-JAN-2006
Fred Green 03-JAN-2006
Fred Green 04-JAN-2006
Bob Red 07-JAN-2006
Bob Red 08-JAN-2006
Bob Red 09-JAN-2006
Alf Blue 12-JAN-2006
Alf Blue 13-JAN-2006


Ideally, it'd be in one query. It looks easier than it is, unless I'm being
dumb.

Thanks you so much if you can help!!

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

asktom.oracle.com is a great place to look for examples of this, and
many other things!

Thanks Scott, I tried Tom, but couldn't find anything that quite worked. I
found examples fof generating lists of days between 2 dates, but nothing
that quite fitted my need. I've done it now with a pipelined function,
following an example found in this newsgroup. cheers for the help.

"Scott" <Scott.Mattes (AT) gmail (DOT) com> wrote

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 (DOT) com> wrote