Use Recursive CTE in DB2 stored proc -

I need to run a recursive CTE within a stored proc, but I can not find it last: SQL0104N An unexpected The following "with" token "SET count = count + 1;" The expected token was found could include: "". Line number = 26

My google-fu showed some similar topics, but none of the resolutions.

Expect the outside of the proc stored in the form of a query function, so I hope there are some syntactic Chinese which I am missing, which will give this work. Similarly, the proc works without compilation and query.

Here is a customized example:

  - Setup table tree (id integer, name varchar (50), parent_id integer); Insert tree values ​​(1, 'Alice', blank); Insert tree values ​​(2, 'bob', 1); Insert tree values ​​(3, 'Charlie', 2);  

-

  - Process to create or change process Test (results) 1 language SQL SQL lecture SQLSTATE CHAR (5); DECLARE SQLCODE integer default 0; Election count INTEGER; Liability balance; Announce total orientation; DECLARE ID INTEGER; Yield to the circle cursor, sysibm.sysdummy1 with return for selection number; Choose the crossover for the cursor to select ID from the order of the tree; SET bomQuery = ''; Prepare stmt from BomQuery; Set count = 0; Set totals = 0; Total set = 0; Open hikers; FIHEEcrackers in ID; WHILE (SQLCODE & lt; & gt; 100) Set number = count + 1; Choose as the ORG (level, id, name, parent_id) (root level of root.id, root.name, root.parent_id tree root where root.id = id union selects all the selected levels + 1, employee .id, Employee .name, employee.parent_ From the organization boss, tree staff where the level 5 & employee parent_id = boss.id) select the count (1) Yoga from ORG; Set = total = yoga; FIHEEcrackers in ID; In the end; Off hiercurs; Open; ED  

Scalar query in DB2 does not seem to recognize the result, and So it will not give selection to the selection (no problem on Oracle or SQL Server) ... instead of the solution open a cursor and (choose instead of INTO).