Re: Building a tree recursively

jparker@epsilon.com wrote: > > (sorry for my lack of syntax here - no FM and no engine or compiler to > test > - Help I'm melting!!) > > I know someone out there must have done this before... > I have a table: > unit_link( > parent_no integer, > child_no integer... > Each child can have children of it's own, for as many levels as > necessary. I tried to build a recursive function call to build the > tree > as follows: > function get_children(unit_no) > declare child cursor for > select child_no > from unit_link > where parent_no = unit_no > foreach child into this_unit > call get_children(this_unit)... > By now you see the problem. On the second time through I get error > -400 > (Fetch attempted on unopen cursor). Tech support says that this is the > way it is designed, you cannot re-use a cursor without first closing it. > It seems to me that each recursive call should be within it's own stack > and that re-using variables, cursors and the like should not be a problem, but... > Is there another way to go about this w/o recursion? What methods have > any of you used to solve similar problems? I've written several recursive programs in 4GL before for customers. Unfortunately I have no example code to hand. You need to declare the cursor once and open it each time with parameters. The trick is to pass the record number to the function as well, and use FETCH ABSOLUTE number. When you get NOTFOUND, then you have gone beyond the leaf node. If I get time I will knock up a quick demo, but I seem to recall making the same threat before and not fulfilling it. ;-) Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+