Re: Finding Children in a nonbinary tree-- a recursive puzzle?

["Fay, Brannon" <brannon.fay@PAETEC.COM>]

If you are using Oracle, look at using START WITH and CONNECT BY PRIOR.
I've used it in the past for this type of hierarchical query, but I don't
understand it well enough to give advice on it.  It should give you what you
need though.

Brannon Fay

-----Original Message-----
From: Love, Kristin [mailto:klove@MSA.COM]
Sent: Tuesday, October 05, 1999 8:52 AM
To: Multiple recipients of list SQR-USERS
Subject: Finding Children in a nonbinary tree-- a recursive puzzle?


I have been working on this puzzle for a while and would like to throw it
open to some larger brains out there....

I am trying to find all the relatives when given a particular unit.

I have a table which looks like this:
 <<...>>

Role =0 means the unit is a parent, role 1 means the unit is a child. So,
for example, when given unit 171054, I want to know that its ancestors were
176040 ("father") and 162281 ("grandfather"). This I can do successfully
with a recursive function:
BEGIN-PROCEDURE FindAncestor(#usn,:#oldest)
BEGIN-SELECT
SerialNo
      show 'Parent         ' $spaces &SerialNo
   Do FindAncestor(&SerialNo,#temp)
   move &SerialNo to #oldest
FROM tablename m
WHERE m.relateid IN ( SELECT relateid
                          FROM tablename
                         WHERE serialno = #usn)
AND role=0
AND m.serialno <> #usn
END-SELECT

END-PROCEDURE FindAncestor

But I seem to be having difficulty going down the tree to find descendants.
The problem is, that when I have a parent with multiple children, the
recursive call only seems to return the set of children, but doesn't take
each child and find its children.

Perhaps this is not the best approach for finding the children. Does anyone
have a fresh idea?

Really, I want to print ALL the relatives on the report, grouped by
relateid. So the report would look like this:

RelateId             SerialNo                 Role
1                      162281                   0
                        170640                   1
........................(more data)
6                      240277                    0
                       240278                     0
                       243341                     1
                       243342                     1

Of course, there would be more info from other tables on this report, like
the unit's name, etc. but I know I need at least this much to start.....

I initially tried to solve this algorithm with connect by, but, as you see,
the unit's parent is not on the table...AND a unit can have multiple
parents.
:) Kristin