Guru: SQL Can Generate A Series
March 16, 2020 Ted Holt
Most of my work is run-of-the-mill, same-old-same-old, more-of-the-same. I’m not complaining. Most of life is mundane and routine, and that’s as it should be. Too much icing ruins the cake. But sometimes I get a challenge, and when that happens, programming can be fun.
Today’s article comes out of such an experience. I needed a table with a week’s worth of dates in it. I could have written an RPG program, but I knew that SQL could handle the task. Today I’ll show you a couple of methods that you can use to generate a series of whole numbers and dates. I’ll leave it to you to adapt it to other data types.
Two Generation Mechanisms
I know of two mechanisms by which to make SQL generate a series — the oracular method (my term for CONNECT BY, because as far as I know, Oracle came up with it) and the recursive common table expression. I’ll demonstrate each mechanism by generating a table of the integers from 1 to 10. Here’s the oracular method.
declare global temporary table Seq as
(with series as
(select level as n
from sysibm.sysdummy1
connect by level <= 10)
select * from series)
with data;
Notice that there is a CONNECT BY clause but no START WITH. Notice also that CONNECT BY references LEVEL, a variable that the database automatically generates. I have read various explanations of how and why this works, but none have ever explained it clearly enough for my satisfaction. The result is this table:
| N |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
That’s one way. You can also use a recursive common table expression to generate the same table.
with series (n) as
(select 1 from sysibm.sysdummy1
union all
select (n + 1) from series
where n < 10)
select * from series
The first SELECT primes the query with the number 1. The second SELECT, which follows UNION ALL, generates rows for values 2 through 10. The resulting table is identical to that of the previous query.
Generating Other Types of Data
You can use these queries as a basis to generate other types of data. Let’s say that given a date, we want to generate a table that contains that date and the six dates to follow — one week of dates. Here’s the oracular method.
declare global temporary table WeekOfDatesA as
(with dates (dt) as
(select date('2020-03-16') + (level -1) days
from sysibm.sysdummy1
connect by level <= 7 )
select * from dates)
with data
with replace;
Here’s the output:
| DT |
| 2020-03-16 |
| 2020-03-17 |
| 2020-03-18 |
| 2020-03-19 |
| 2020-03-20 |
| 2020-03-21 |
| 2020-03-22 |
This is a variation of the method that generates the series of integers. I used the generated integer value for date arithmetic to generate the dates.
I am not going to present the recursive CTE method. I can make it work, but I don’t like any variation that I’ve come up with. If you have a clean method, please let me know.
Just For Grins
Please permit me to share an illustration that is, as far as I know, of no practical value. I stumbled upon it one day when looking for something else. It is an SQL solution to the eight queens puzzle.
I was introduced to the eight queens puzzle in the university, when I was pursuing my degree in computer science. I had to write a Pascal program to solve it. It never occurred to me that SQL could be used to solve the puzzle, but one day I found it mentioned on Quora.
The challenge is to place eight queens on a chessboard in such a way that no two queens threaten one another. That means that no two queens can be on the same rank (row), same file (column), or same diagonal. There are 92 solutions. Here’s one of them:
Here’s a solution I came up with.
with ta as (select level as a
from sysibm.sysdummy1
connect by level <= 8),
tb as (select a as b from ta),
tc as (select a as c from ta),
td as (select a as d from ta),
te as (select a as e from ta),
tf as (select a as f from ta),
tg as (select a as g from ta),
th as (select a as h from ta)
select a, b, c, d, e, f, g, h
from ta cross join tb
cross join tc cross join td
cross join te cross join tf
cross join tg cross join th
/* check for same rank (row) */
where a <> b and a <> c and a <> d and a <> e
and a <> f and a <> g and a <> h and b <> c
and b <> d and b <> e and b <> f and b <> g
and b <> h and c <> d and c <> e and c <> f
and c <> g and c <> h and d <> e and d <> f
and d <> g and d <> h and e <> f and e <> g
and e <> h and f <> g and f <> h and g <> h
/* check for same ascending diagonal */
and a+1 <> b and a+2 <> c and a+3 <> d
and a+4 <> e and a+5 <> f and a+6 <> g
and a+7 <> h and b+1 <> c and b+2 <> d
and b+3 <> e and b+4 <> f and b+5 <> g
and b+6 <> h and c+1 <> d and c+2 <> e
and c+3 <> f and c+4 <> g and c+5 <> h
and d+1 <> e and d+2 <> f and d+3 <> g
and d+4 <> h and e+1 <> f and e+2 <> g
and e+3 <> h and f+1 <> g and f+2 <> h
and g+1 <> h
/* check for same descending diagonal */
and a-1 <> b and a-2 <> c and a-3 <> d
and a-4 <> e and a-5 <> f and a-6 <> g
and a-7 <> h and b-1 <> c and b-2 <> d
and b-3 <> e and b-4 <> f and b-5 <> g
and b-6 <> h and c-1 <> d and c-2 <> e
and c-3 <> f and c-4 <> g and c-5 <> h
and d-1 <> e and d-2 <> f and d-3 <> g
and d-4 <> h and e-1 <> f and e-2 <> g
and e-3 <> h and f-1 <> g and f-2 <> h
and g-1 <> h
order by a, b, c, d, e, f, g, h
- I use the oracular method to generate the numbers 1 through 8 in common table expression TA.
- I duplicate TA into seven more common table expressions: TB-TH. This takes care of no two queens being in the same file (column).
- I cross join all eight common table expressions. This generates all possible ways to place eight queens on a chessboard.
- The WHERE clause ensures no two queens are on the same rank (row) or same diagonal.
This query finds all 92 solutions to the puzzle.
If you have a different solution, please let me know. I’d like to see it.
Writing a query to solve the eight queens puzzle may or may not be practical — I don’t know — but maybe it shows us that SQL is capable of doing more than what we use it for.
