Re: Generate temporary table/list of dates

[Pete Helgren <Pete@xxxxxxxxxx>]

Thanks Elvis.  The procedure worked great.

Pete


Elvis Budimlic wrote:
> 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
>
>
>