Win a copy of Hands On Software Engineering with Python this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Bear Bibeault
  • Knute Snortum
  • Liutauras Vilda
  • Tim Cooke
  • Devaka Cooray
  • Paul Clapham
Saloon Keepers:
  • Tim Moores
  • Frits Walraven
  • Ron McLeod
  • Ganesh Patekar
  • salvin francis
  • Tim Holloway
  • Carey Brown
  • Stephan van Hulst

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

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:


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
Posts: 23963
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.

Not so fast naughty spawn! I want you to know about
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!