• 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:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

SQL Recursion woes

 
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a table that stores tree structures, the tree name and nodes in the tree with their respective parent node. Here's an example:

Tree Name | Node Name | Parent Node Name
-------------------------------------------------------
Business | Group A |
Business | Group Aa | Group A
Design | Group A |
Design | Group Aa | Group A

As you can see, the Node Names are reusable between the trees. I'm trying to recurse through this table to display the structure of each tree. Unfortunately, since the Node Names are reused between the trees, using a Connect By Prior Node Name = Parent Node Name is causing it to skip between the trees as it recurses. What I need to do as I connect to a lower recursion level is to make sure that the Tree Name has not changed so as to make sure it's still recursing the same tree. Here is the SQL that I have, and SQL Developer takes it as a valid SQL:

select TreeName, NodeName
from TreeTable
start with TreeName in (select distinct TreeName from TreeTable)
connect by prior TreeName = TreeName
connect by prior NodeName = ParentNodeName

However this isn't solving the problem. It's still skipping between trees. From what I can tell, even though our Oracle 10g says this is a valid SQL, it's ignoring all but the last Connect By Prior clause. Any help would be appreciated.

P.S. using Start With TreeName = 'Business' does not help as this constraint only applies at the start of the recursion, as the command name implies. Once it goes down to Level 2 and below it starts skipping between the trees.
 
Matthew Snow
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Using a AND PRIOR element = element will solve the problem.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic