Win a copy of OCP Java SE 8 Programmer II Exam Study Guide this week in the OCP forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Can't get recursive CTE to work [DB2, SQLCODE=-345, SQLSTATE=42836]  RSS feed

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey guys,

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:

BNTZ_AKTN_ID (PK)
BNTZ_AKTN_ID_PA (FK)
BNTZ_AKTN_BSBG

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?

Best regards
 
Sheriff
Posts: 23451
46
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi John, welcome to the Ranch!

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.

 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!