Not all recursive SQL statements need to be complex.
Here is a small recursive SQL I used a few years ago.
I had a file that contained information on a 15 minute interval.
I needed to pull the information from the file for all 15 minute intervals, even though there may be gaps in the data.
So this recursive statement builds all of the 15 minute periods between two timestamps:
WITH TS_DIM2 (HTS) AS (
VALUES (TIMESTAMP('2015-06-14 00:00:00.000000') )
UNION ALL SELECT HTS + 15 MINUTES FROM TS_DIM2 WHERE HTS < TIMESTAMP('2015-06-15 14:45:00.000000'))
, TS_DIM (HDATE, HTIME)AS (SELECT DISTINCT DATE(HTS), TIME(HTS) FROM TS_DIM2)
SELECT * FROM TS_DIM
I've even been able to find a use for a recursive statement that just generates a list of the letters in the alphabet.
These may seem overly trivial, but you can use these types of statements as a CTE of keys. Then Left Join or Exception Join to other data and be able to determine when you are missing certain keys.
Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Hoteltravelfundotcom
Sent: Thursday, June 29, 2017 11:42 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Recursive SQL on the i
HI I was interested in how you might be using Recursive SQL on the i.
From what I have read on the IBM web site on this topic,. it seems most used in very large enormous, complex, data sets.
My way has always been to break the task into smaller manageable parts.
Perhaps someone who really knows can explain this better.
Thank you,
Joe
midrange.com
