SQL Question

I have a table where the hierarchy is built by storing the parent_id in the same table.... id, parent_id .... other fields Can someone suggest an SQL statement that will list the entire hierarchy as follows id1 id1.1 id1.2 id1.2.1 id1.2.2 id1.2.3 id1.2.3.1 id1.3 id1.4 id2 .... Thanks!

I'd use SPL and a bit of recursion, or if you are feeling brave you could use the node datablade Rajesh Kapur wrote: > > I have a table where the hierarchy is built by storing the parent_id in the > same table.... > > id, > parent_id > .... other fields > > Can someone suggest an SQL statement that will list the entire hierarchy as > follows > > id1 > id1.1 > id1.2 > id1.2.1 > id1.2.2 > id1.2.3 > id1.2.3.1 > id1.3 > id1.4 > id2 > .... > > Thanks! -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

On Wed, 05 Nov 2003 13:07:23 -0500, Rajesh Kapur wrote: SQL is REALLY bad at recursive data structures, as attractive as they are to programmers. Recognising this Oracle (yes Mark I'll even give credit to the big 'O' when it's appropriate, I just don't get the opportunity often ;-} ) years ago added extensions to their SQL to permit processing these babies in a single SQL. Unfortunately no other SQL implementation has added that feature so you'll have to implement it in code in you app or in an SPL. Another approach would be to code a UDF in 'C' or Java which might be more efficient, assuming you have 9.xx. Art S. Kagel > I have a table where the hierarchy is built by storing the parent_id in the > same table.... > > id, > parent_id > .... other fields > > Can someone suggest an SQL statement that will list the entire hierarchy as > follows > > id1 > id1.1 > id1.2 > id1.2.1 > id1.2.2 > id1.2.3 > id1.2.3.1 > id1.3 > id1.4 > id2 > .... > > Thanks!

> Unfortunately no other SQL implementation has added that feature so you'll have > to implement it in code in you app or in an SPL. Hmm - well, IBM DB2 HAS implemented recursive common table expressions (which IS in the standard and IS potentially a better implementation that what we implemented in Oracle many,many years ago). Of course, I don't believe that Informix has implemented this yet. How's that for truth in advertising ? Recursive CTE's are actually pretty cool - see http://www7b.software.ibm.com/dmdd/library/techarticle/0307steinbach/0307steinbach.html#section1 for a discussion of them, and how they relate to Oracle's CONNECT BY capabilities.

Mark Townsend wrote: >> Unfortunately no other SQL implementation has added that feature >> so you'll have to implement it in code in you app or in an SPL. > > Hmm - well, IBM DB2 HAS implemented recursive common table expressions > (which IS in the standard and IS potentially a better implementation > that what we implemented in Oracle many,many years ago). Of course, I > don't believe that Informix has implemented this yet. Correct, though as Paul Watson mentioned, both SPL and the Node Datablade can be used to achieve the same result. > How's that for truth in advertising ? Not bad! My understanding (based on second-hand knowledge and hence subject to correction) is that the limitations on CONNECT BY PRIOR etc are quite severe - and the result isn't a relation since the order of the data is critical. However, even that is arguably better than nothing. > Recursive CTE's are actually pretty cool - see > http://www7b.software.ibm.com/dmdd/library/techarticle/0307steinbach/0307steinbach.html#section1 > for a discussion of them, and how they relate to Oracle's CONNECT BY > capabilities. Or http://tinyurl.com/tux0 -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/