with temp (num)
as (select 5 from sysibm.sysdummy1
select num + 1
where num < 37)
select * from temp
... it generates a list of numbers (from 5 to 37 inclusive, in this case). Isn't that cool ?
Errr ... well, it is to me, since I seem to spend quite a bit of my time writing convoluted SQL queries on data warehouses. I originally saw something like it used to generate a list of all the dates and week numbers for a particular range, to then join against an auditing table and generate useful reports and things.
(Ok, an Excel pivot table, because that's how the users seem to prefer things. Oh well ...)