We are implementing hierarchy in our app using the adjacency model. We have been using CONNECT BY PRIOR to retrieve the leaf nodes and traverse through the hierarchy.
We need to develop a SQL function that can be database agnostic. But first we want to do that with Oracle. Can you give me pointers regarding a recursive function to traverse through the hierarchy?
Requires Free Membership to View
My table structure is very simple:
Node ID Parent ID <Node Attributes1-5>...
Your response is highly appreciated.
If you’re already on Oracle, why do you wish to be database-agnostic? Once you choose a database platform, you should leverage its unique capabilities to your fullest advantage.
If you have SQL2000 in-house, then you already know it doesn’t support hierarchical queries. You have to simulate them procedurally.
The least database-dependent method I can think of is to do an iterative scan, instead of using the hierarchy clauses (start with, connect by). This requires you to keep some housekeeping variables on hand. You can still do it recursively. At least then the algorithm you develop can be readily translated between PL/SQL and T-SQL (or whatever procedural database language you’re using). Your function will be calling itself, so it needs a reliable method of terminating the recursion.
This was first published in January 2010
Join the conversationComment
Share
Comments
Results
Contribute to the conversation