I am tring to execute the following SQL statements through the Iseries
Navigator for DB2/V8.2, But i come up with an error saying recursion is
not allowed in common table expression. This is a example i picked up
from SQL cook book. I am not sure where i am wrong.
Any help is appreciated
WITH TEMP ( SUPV_ID,EMPID, FIRSTNAME) AS
(
SELECT TV.SUPV_ID,TV.EMPID, TV.FIRSTNAME
FROM TRNWORK.TRAINEE_MASTER_VIEW TV
WHERE TV.SUPV_ID = '1777'
UNION ALL
SELECT T.SUPV_ID , T.EMPID, T.FIRSTNAME FROM
TRNWORK.TRAINEE_MASTER_VIEW C , TEMP P WHERE P.EMPID = C.SUPV_ID
)
SELECT EMPID, SUPV_ID, FIRSTNAME FROM TEMP;
================================================== ===
---Error------------
SQL State: 42836
Vendor Code: -346
Message: [SQL0346] Recursion not allowed for common table expressions.
Cause . . . . . : The common table expression specified is not valid.
The subselect for table TEMP refers to itself. Recursive common table
expressions are not allowed. Recovery . . . : Change the common table
expressions to refer to a table that exists or a common table
expression that has already been defined. Try the request again.