error in using the recursive CTE to get the folder path from a table in DB2?

Question

I have a table with columns - OBJECT_ID, NAME, PARENT_CONTAINER_ID. This Table contains all the folder names and their ParentID. If the Parent ID is null then it's root folder. I need to pull a report of all the folders along with their full path. But the following query throws error..

WITH containercte(OBJECT_ID, NAME, PARENT_CONTAINER_ID, LEVEL, treepath) AS
(SELECT OBJECT_ID, NAME, PARENT_CONTAINER_ID, 0 AS LEVEL, CAST(NAME AS VARCHAR(1024)) AS treepath FROM CONTAINER 
WHERE PARENT_CONTAINER_ID IS NULL
UNION ALL 
SELECT d.OBJECT_ID AS OBJECT_ID, d.NAME, d.PARENT_CONTAINER_ID,
containercte.LEVEL + 1 AS LEVEL,
CAST(containercte.treepath + '-> ' + CAST(d.NAME AS VARCHAR(1024)) AS VARCHAR(1024)) AS treepath FROM CONTAINER d
INNER JOIN containercte
ON containercte.OBJECT_ID = d.PARENT_CONTAINER_ID)
SELECT * FROM containercte ORDER BY treepath;

Can someone let me know what's wrong in this query? the error it throws is

"The fullselect of the recursive common table expression "CONTAINERCTE" must be the UNION of two or more fullselects and cannot include column functions, GROUP BY clause, HAVING clause, ORDER BY clause, or an explicit join including an ON clause"

Answer 1

IN Db2 LUW you are not allowed to use the explicit join syntax in recursive common table expressions. They are ok elsewhere but recursive CTE are the exception.

Rewrite the query to use the old join syntax Rewrite

FROM CONTAINER d
INNER JOIN containercte
ON containercte.OBJECT_ID = d.PARENT_CONTAINER_ID)

to

FROM CONTAINER d,
containercte
WHERE containercte.OBJECT_ID = d.PARENT_CONTAINER_ID
  AND ...

And I would suggest to add a stop condition in the WHERE clause as well something like

AND LEVEL < 8