Recursion has a more benevolent name...

[efnkay]

The word "recursion" is a little scary. The technique actually being described (the WITH part) is called a "common-table-expression" and it can be used to join with any "select" statement it precedes.

This technique works well when you need to group "detail" information with "header" information particularly when the data is from the same table.

[J.Pluta]

You're technically correct in that recursion is achieved by way of a CTE (Common Table Expression), but to be completely accurate recursion is not the same as a CTE. As you noted you can use a CTE for many things; for example, I like to use it to break up complex queries into manageable, testable components. Recursion is a very specific subset of a CTE, specifically joining a file to itself. Recursion is relatively new to DB2 for the i (by "relatively new" I believe it was enabled in V5R4, although I'm not certain and I don't have a V5R3 machine on which to test that premise).

Joe

[efnkay]

Your right about it being a fairly recent capability. I found this blurb about "recursive" common-table-expressions in the V5R2 DB2 Universal Database for ISeries SQL Reference, so it came along sometime after:

If a fullselect of a common table expression contains a reference to itself in a FROM clause, the common table expression is a recursive table expression. Recursive common table expressions are not supported in DB2 UDB for iSeries.