Recursion through Common table expression

[Endif]

I am tring to excute the following SQL statements through the Iseries Navigator. But i come up with an error saying recursion is not allowed in common table expression.

Kindly help


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;

 

[PruSQLer]

Could it be because you aliased the table in the second part of the UNION ALL with a 'C' but are referring to it as 'T' ?

 

[MarcLodge]

In addition to the mistake spotted by PruSQLer, could it also be that the second part of the UNION ALL refers to the table TEMP which is, unless I have read it incorrectly, the table you are attempting to define. Have you specified the table TEMP P correctly in that part of the SQL?

Marc