Q

How to use iterative scans to create hierarchical queries in SQL

PL/SQL expert Dan Clamage explains how to use iterative scans to create hierarchical queries in SQL that are database agnostic.

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?

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.

Have a question for Dan Clamage? Send an e-mail to editor@searchoracle.com

.

 

 

This was first published in January 2010

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close