Re: need a SELECT script.
In article <6raa1i$1j1$1@news.xmission.com>, mdstock@informix.com (Mark D.
Stock) wrote:
>
> DK wrote:
> >
> > I've a table which represents a tree structure:
> >
> > child_id | parent_id
> > 2 1
> > 3 2
> > 4 3
> > 8 3
> > 5 4
> > 6 4
> > 9 8
> >
> > I need to select the
> > 1. parent
> > 2. child nodes
> >
> > of any given node.
>
> RECURSION! Excellent! I accept the challenge. Don't know why, but if I
> can do it in 4GL, then why not SPL.... Okay, okay, I'll read that list
> later. :-)
>
> So, starting with the following I presume:
>
> ------------------------------------------------------------------------
> CREATE TABLE object
> (
> child_id SMALLINT,> parent_id SMALLINT
> );
> ------------------------------------------------------------------------
> INSERT INTO object VALUES (2, 1);
> INSERT INTO object VALUES (3, 2);
> INSERT INTO object VALUES (4, 3);
> INSERT INTO object VALUES (8, 3);
> INSERT INTO object VALUES (5, 4);
> INSERT INTO object VALUES (6, 4);
> INSERT INTO object VALUES (9, 8);> ------------------------------------------------------------------------
>
> > Like if I choose to find the parent of node=9 then i should get the
> > results as
> >
> > 8
> > 3
> > 2
> > 1
>
> So being simplistic to start with, I assumed each child had only one
> parent:
>
> ------------------------------------------------------------------------
> CREATE PROCEDURE get_parent(child_id SMALLINT)
> RETURNING SMALLINT;> DEFINE parent_id SMALLINT;
>
> LET parent_id = 0;
> SELECT object.parent_id
> INTO parent_id
> FROM object
> WHERE object.child_id = child_id> ;
>
> IF parent_id != 0
> THEN
> RETURN parent_id WITH RESUME;
> FOREACH EXECUTE PROCEDURE get_parent(parent_id) INTO parent_id
> RETURN parent_id WITH RESUME;
> END FOREACH;
> END IF;
>
> END PROCEDURE;
> ------------------------------------------------------------------------
> EXECUTE PROCEDURE get_parent(9);> ------------------------------------------------------------------------
>
> Which gives the desired results.
>
> > and if I want to get the children of the node=3, then my result should
> > be like
> >
> > 4
> > 5
> > 6
> > 8
> > 9
>
> And a global replace of parent for child and child for parent, in three
> passes you understand, gives...... a slight problem. But if we add one
> more loop to handle multiple children, we get:
>
> ------------------------------------------------------------------------
> CREATE PROCEDURE get_child(parent_id SMALLINT)
> RETURNING SMALLINT;> DEFINE child_id SMALLINT;
>
> LET child_id = 0;
> FOREACH SELECT object.child_id
> INTO child_id
> FROM object
> WHERE object.parent_id = parent_id
>
> IF child_id != 0
> THEN
> RETURN child_id WITH RESUME;
> FOREACH EXECUTE PROCEDURE get_child(child_id)
> INTO child_id
> RETURN child_id WITH RESUME;
> END FOREACH;
> END IF;
> END FOREACH;
>
> END PROCEDURE;
> ------------------------------------------------------------------------
> EXECUTE PROCEDURE get_child(3);> ------------------------------------------------------------------------
>
> Which also gives the desired results.
>
> > (I can read the results off the cursor?)
>
> Indeed.
>
> > I need a select query/script which can do this work and I can tune it
> > to
> > my
> > requirements. Any suggestions will be welcome and if possible please
> > e-mail me
> > a copy of the solution at dk@pcisys.net
>
> That's my first pass. It can probably stand improvement.
>
> Hope it helps,
> --
Assuming that it work, and the SA contractors I've been working with that
Mark is reasonably intelligent, then my only proviso is to limit the
recursion depth. One site I was working was regularly blowing 7.20 apart
with a recursive SPL.
Paul Watson # I don't suffer from
WF Software Ltd. # stress, I'm just
Tel. (+44) 1436 674729 # a carrier
Fax. (+44) 1436 678693 #