LEVEL

Restriction: This topic applies to Windows environments only.
ANSI DB2 SQL/DS XDB
      X

The LEVEL condition may be used in conjunction with each PREVIOUS function to qualify the result on the basis of the number of recursions performed. Any of the operators =, <, <=, >, >=, != or ^= may be used in the LEVEL condition, and the right hand side may be either a positive integer (level numbers begin with 0) or the keyword BOTTOM, which indicates the bottom level of each of the recursive branches constructed by the PREVIOUS function.

All other search conditions (except those applied by GROUP BY and HAVING clauses) are applied to the original table before the recursion is performed. After the table has been "exploded" GROUP BY, HAVING and ORDER BY clauses are applied.

For example, if you wanted to know only the second level subparts used in making product P11 (see PREVIOUS function), you could execute the following query. Note that level numbers begin with 0, therefore the second level is level 1.

SELECT subpart
FROM subpart
WHERE product = PREVIOUS(subpart)
  AND LEVEL = 1
  AND product = "P11"

The result of this query is:

subpart
P5
P7
P8
P1
P2

For a graphic depiction of the various levels of recursive inference in the above example, see the figure in the PREVIOUS function description.