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

[Tony DeLia <tdelia@EROLS.COM>]

Hi Kristin,
   I posted something similar on my website... http://www.sqrtools.com
(OR http://www.sqrtools.com/tdsvc.htm for direct access to the page)

In my example I establish the "tree" hierarchy using the
EMPLID->SUPERVISOR_ID relationship found in  the EMPLOYMENT table in
HR... Here's a sample of the output (HR Demo data)...

     8001 - Schumacher,Simon
          8102 - Sullivan,Theresa
               8406 - Davis,William D.
                    8517 - Holden,Peter
                    8664 - Carroll,Brucest
                    8668 - O'Brien,Michael
                    8730 - Smithers,Jasmine
                    8761 - Vallero,Joaquin
                    G010 - Masters,Jason
               8409 - Westlund,Julie
               8516 - Akers,Danielle
                    8317 - Norton,Terry
                    8421 - Jaegar,Annette
                         8832 - Tanner,Doris
            ...etc....

The high-level id '8001' was entered and all the sub-ordinate or 'child'
rows were produced in tree format... No 'true' recursive procedures were
required to accomplish this...

This could be easily modified for your purposes... your relationships
are a little different but can be accounted for in the first portion of
the program (Process-Phase1)...

    Hope this helps,
                         Tony DeLia

PS - I expanded this procedure last week (not on website) to produce a
Tree Structure (pstreestrct, pstreedefn, pstreenode, etc...) for use by
an external Lotus Notes application for security... it allows
Supervisors to access Salary Planning panels for all subordinates... the
tree structure can be refreshed any time due to a change in
Supervisors... works great!

Love, Kristin wrote:
>
> 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

--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com
http://www.sqrtools.com