Recursion not allowed for common table expressions error

[Endif]

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.

[Karl Hanson]

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