I'm querying some data from a table in DB2/z which holds hourly data but occasionally skips some hours (if the clients don't send up details for that hour).
Since I'm feeding the data straight into a charting tool which needs a query to get the axis data, it sometimes has missing values from the time axis and looks ugly:
23.00 |===
22.00 |=====
14.00 |============
01.00 |==
00.00 |=
+--------------------
We've been verboten from adding any helper tables like a 24-row table holding all the hours so I need a query in DB2 that will return all the hours of the day without a reliable backing table. I've been using the monstrous:
select '00.00' from sysibm.sysdummy1
union all select '01.00' from sysibm.sysdummy1
union all select '02.00' from sysibm.sysdummy1
union all select '03.00' from sysibm.sysdummy1
: : :
union all select '23.00' from sysibm.sysdummy1
order by 1
which is about as kludgy a solution I've ever seen, so I'd rather have something a little cleaner.
I've posted my own solution below but I'm open to anything simpler or more elegant.
The following level-limited recursive call will give the desired range without a backing table. Without the limit on level, DB2 complains that the function may be infinitely recursive.
with trange(lvl, tm) as (
select 1, time('00:00') from sysibm.sysdummy1
union all select lvl+1, tm + 1 hour from trange where lvl < 24
) select left(char(tm,iso),5) as hour from trange;
This gives:
HOUR
-----
00.00
01.00
02.00
03.00
: : :
22.00
23.00
as desired.