Win a copy of Learning OpenStack Networking: Build a solid foundation in virtual networking technologies for OpenStack-based clouds this week in the Cloud/Virtualization 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 ...
Marshals:
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

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: 23706
50
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.

 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!