By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,274 Members | 2,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,274 IT Pros & Developers. It's quick & easy.

Recursion not allowed for common table expressions error

P: n/a
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.

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Endif wrote:
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.


Recursive common table expressions are not currently supported by DB2
UDB for iSeries - it is a known requirement.

Fyi: Since DB2 on iSeries is not a separate product, its version is the
same as the operating system. DB2/V8.2 is a release of DB2 UDB for LUW.
The latest release of i5/OS (formerly OS/400) is V5R3.

http://www.ibm.com/servers/eserver/iseries/db2/

--
Karl Hanson
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.