Recursive DECLARE CURSOR statement (SAP Library

Recursive DECLARE CURSOR statement

The recursive DECLARE CURSOR statement can be used to receive bills of material by means of a command.

Syntax

<recursive declare cursor statement> ::= DECLARE <result table name> CURSOR FOR
WITH RECURSIVE <reference name> (<alias name>,...) AS
(<initial select> UNION ALL <recursive select>) <final select>

result table name, reference name, alias name, query spec, select statement

DECLARE C CURSOR FOR
WITH RECURSIVE PX (MAJOR, MINOR, NUMBER, MAINMAJOR) AS
(SELECT W,X,Y,W FROM T WHERE W = 'aaa' UNION ALL
SELECT W,X,Y,MAINMAJOR FROM T, PX WHERE MINOR = T.W)
SELECT MAINMAJOR,MINOR,NUMBER FROM PX ORDER BY NUMBER

Explanation

The QUERY specification initial_select is executed and the result is entered in a temporary result table whose name is defined by specifying the reference name. The column names contained in it receive the names from the list of alias names. The number of output columns in the QUERY specification must be identical to the number of alias names.

The QUERY specification recursive select should comprise a SELECT statement that contains at least the reference name in the FROM clause and one JOIN predicate between this table and a different table from the FROM clause.
The QUERY specification recursive select is repeated until it does not produce a result. The respective results are (logically) entered in the temporary result table whose name is defined by the reference name. This table is extended continuously. It is ensured, however, that the results of the n ^th execution are used for the n+1 ^th execution to avoid an endless loop.

The SELECT statement final select must only contain one QUERY expression that comprises a QUERY specification.
This is a SELECT statement across the table with the specified reference name in which the following elements can be used: set function names, GROUP clause, HAVING clause, ORDER clause, LOCK option

If a result table name with the specified reference name existed before the recursive DECLARE CURSOR statement was executed, the corresponding cursor is closed implicitly.