Re: Another use of SQL RAND - a SQL recursive query

I do not know how well I could explain recursive queries, but what was your stated understanding, seems to do some justice, at least for the simple case of generating successive rows from one row. However, that example has no specific join selection back to the prior generated data and its initial set is only the one row. The prior set of rows that were generated are visible to the set which is currently being generated into the temporary result table. In this case, both the first and each successive row being generated is [a set of] one row; being generated and then included by the UNION ALL.

In this case the new\generated row has a value that is just a counter to enable stopping the generation of new rows. A different method to stop could be employed, having generated new rows that have no real relationship to to the result set of the prior iteration. Take for instance the given example, use WHERE RANV NOT IN (0, 1) as the escape from generating more rows, instead of RNum<100; i.e. stop adding of rows when an inclusive boundary value is generated by the RAND().

First the WITH statement establishes a CTE [Common Table Expression] against which to recurse
Second there is the first row(s) generated in the CTE, called the /initialization fullselect/ to which recursive UNION ALL data will be appended
Third there is a UNION ALL to include the already generated first row(s), requesting to combine the additional generated rows for each successive iteration
Fourth there is the recursive reference [to itself, to the CTE] with some logic to generate more rows and [there should be] logic to stop generating, called the /iterative fullselect/; run until either no further matches in a join or the selection excludes the rows being generated

The following query generates for its initialization, the three odd integer values of 1, 3, & 5. Then it generates +1 values for each of those values, recursively generating plus-one results of the prior set up to the integer value seven. The query results are shown in report form later, but also shown here another way. The three odd values,then each adding one, produces the following results; each set divided by a semicolon:
1 3 5; 2 4 6; 3 5 7; 4 6; 5 7; 6; 7;

Represented another way, each set on a new line:
The initial set is 1 3 5
1st generated set is 1+1 3+1 5+1
2nd generated set is 2+1 4+1 6+1
3rd generated set is 3+1 5+1 /* 7+1 is excluded per *GT 7 */
4th generated set is 4+1 6+1
5th generated set is 5+1 /* 7+1 is excluded per *GT 7 */
6th generated set is 6+1

<code>
with
three_odds (oddval) as
( select int(1) from sysibm.sysdummy1
union all
select oddval+2 from three_odds
where oddval < (7-2)
)
,uptoseven (oddplusone) as
( select oddval from three_odds
union all
select int(oddplusone+1) from uptoseven
where oddplusone < 7
)
select oddplusone from uptoseven

....+....1....
ODDPLUSONE
1
3
5
2
4
6
3
5
7
4
6
5
7
6
7
******** End of data
</code>

Regards, Chuck