i'm trying to write a query for a table i'd like to get a tree.
So i'd like to get all children of a parent recursively.
To simplify it, lets say my table has 3 columns:
So BNTZ_AKTN_ID_PA refers to the parent ID.
So i read a bit about recursive common table expressions and came up with this sql:
However i get the following error:
DB2 SQL Error: SQLCODE=-345, SQLSTATE=42836, SQLERRMC=Q123456.FTREE, DRIVER=3.63.118
Anyone can help me, why this is not working at all?
I googled that error code and I found this page about what it means in DB2:
The common table expression name includes a reference to itself and therefore:
-- must be the union of two or more fullselects.
-- cannot include a GROUP BY clause.
-- cannot have column functions. v cannot include a HAVING clause.
-- and cannot include an explicit join with an ON clause.
System action: The statement cannot be executed
Programmer response: Change the common table expression by:
-- making it a union of two or more fullselects.
-- removing any column functions, GROUP BY clause, HAVING clause, or explicit JOIN including an ON clause.
-- removing the recursive reference.
It looks to me like your JOIN including the ON clause is perhaps in the wrong place? I'm just throwing that out as a tentative idea because I've never attempted a recursive query myself. But maybe somebody else will see this post and have a better idea.