Re: Generate temporary table/list of dates

Something like this on V5R4 and up (leveraging recursive CTEs):

WITH DateList (tempDate) AS (SELECT date('2008-06-30') from sysibm/sysdummy1 UNION ALL SELECT tempDate + 1 day FROM DateList WHERE tempDate + 1 day < date('2008-07-15')) SELECT tempDate FROM DateList
As for V5R3, I don't think you can get away from a temporary (or permanent)
table of some sorts.
Here is a version with a temporary table:

/* one time step - create the procedure */
CREATE PROCEDURE dateRange(startingDate DATE, endingDate DATE)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE temp DATE;
DECLARE listDates CURSOR FOR SELECT F1 FROM SESSION/DateList;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710'
DELETE FROM SESSION/DateList;
DECLARE GLOBAL TEMPORARY TABLE SESSION/DateList (f1 DATE);
SET temp = startingDate;
WHILE temp <= endingDate DO
INSERT INTO SESSION/DateList VALUES(temp);
SET temp = temp + 1 DAY;
END WHILE;
OPEN listDates;
END;

/* call the procedure to return the list of valid dates */
CALL dateRange(DATE('2008-06-30'),DATE('2008-07-15'));

If performance is of concern, you could create a table with list of all
valid dates once and then your queries could be directed against it. No
need for stored procedure then, except for perhaps the initial step of
populating the static table. I'd create an index over the field in the
static table. Queries against ought to fly then.

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Generate temporary table/list of dates

I would like to generate a list of valid dates within a given range of date on the fly, if possible. I am running V5R3M0. I found a couple of examples out in cyberspace but couldn't get them "translated" into DB2/400 syntax. One used a recursive CTE which I don't think is supported on V5R3M0.

Anybody got something like this? I'd rather not create a table and populate it if I can avoid it (unless it is a temporary table discarded after the query runs).

Thanks,

Pete