Re: Building a tree recursively

Hi Scott, you could use the code you wrote in a Stored Procedure (which can be executed recursively). The following example retrieves all the children, grandchilds, grand-grandchilds... (including their parents): create procedure get_children(unit_no int) returning int,int ;define v_child,v_parent int; foreach select child into v_child from unit_link where parent = unit_no if v_child = unit_no then return; --to prevent infinte loops return unit_no,v_child with resume; foreach execute procedure get_children(v_child) into v_parent,v_child return v_parent,v_child with resume; end foreach end foreach; end procedure; Have fun, Peter _________________ Mag. Peter Kolmhofer DBA Porsche Informatik (Austria) Scott Black wrote: : 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? : TIA : BTW this is HP-UX 10.20 OnLine 7.22 4GL 6.04