Series of Dates

[VergeResources]

I'm trying to come up with a way to return back a series of dates. For example, I am trying to return a separate record for each of the last x days:

DATE
-----
2004-07-15
2004-07-14
2004-07-13
...

If I could just come up with a statement that returns an incrementing sequence of numbers, I could use it to build dates (SYSDATE - x DAYS or some such logic). I just can't think of a way to return a list of numbers. Any ideas?

 

[blom0344]

Can you use:

ROW_NUMBER() OVER ()

If your platform allows these OLAP functions you will get the data returned in no particular order.
You will probably want to order the output by the date itself (so add an 'order by' on the date field in the SQL)

T. Blom
Information analyst
tbl@shimano-eu.com

 

[sathyarams]

Or you can use recursive SQL this way :

with temp(a)
as
(
values(date('2004-01-01'))
union all
select a+1 day from temp where a < '2004-12-31'
)
select * from temp

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[sathyarams]

oooch .... the above query I posted generates the dates in ascending order ... I guess you can modify it for descending sequence ..

Cheers
Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[VergeResources]

Thanks for the tips - unfortunately, I don't think either one will work for me. I'm using DB2 on the mainframe OS/390, and it does not support the ROW_NUMBER OLAP function.
The second suggestion would probably work in a stored procedure, but I need to come up with a simple SQL query that I can use as the source for a Crystal Report. But, the Union is an interesting idea...

Thanks again!