Re: Building a tree recursively

(sorry for my lack of syntax here - no FM and no engine or compiler to test - Help I'm melting!!) Best method is to use a stored procedure, you can recurse there. It goes something like: explode() foreach select * from table where parent = parent_key return record with resume call explode(child_key) end foreach You then just open that in 4gl as a cursor, If you don't want to go that route, another method I have seen work is DEFINE part_list ARRAY[n] OF RECORD parent ... child ... whatever else end record, next_key, last_key ... LET s1 = "SELECT whatnot FROM part_list WHERE parent = '?'" PREPARE stmt FROM s1 DECLARE b_curs CURSOR FOR s1 LET last_key = 2 LET next_key = 1 LET part_list[1].parent = parent_key # the root key you are starting with WHILE last_key > next_key OPEN b_curs USING parent_key[next_key] FOREACH b_curs INTO part_list[last_key].* LET last_key = last_key + 1 END FOREACH LET next_key = next_key + 1 END WHILE So what happens is that you keep filling the end of the array (up to last_key) with the children of each element from next_key. When next_key catches up to last_key you're done. Final approach is a banding approach - where you read each level of the parts list on your way down: SELECT * from part_list WHERE parent = 'root_value' INTO temp t1 INSERT INTO t2 SELECT * FROM t1 INSERT INTO t3 SELECT * FROM t2 LET read_count = 99999 WHILE read_count > 0 update statistics for table t2 SELECT * from part_list WHERE parent IN (select key from t2) into tempt1 LET read_cont = SQLCA.SQLERRD[3] INSERT INTO t3 SELECT * FROM t2 DROP TABLE t2 SELECT * FROM T1 INTO TEMP t2 DROP TABLE t1 END WHILE At the end you've got all of your children in t3. You can also add in a level indicator into that one. Mind you I've never tried this approach - I just thought of it one day in the shower and resolved to try it the next time I ran into this type of situation again. Logically this should be more efficient since you're not strictly walking each branch of the tree, but rather stripping huge chunks of it at once. Of course you lose any linkage between the child and the parent with this approach. Please respond to Scott Black <sblack@elsouth.com> To: informix-list@rmy.emory.edu cc: (bcc: Jack Parker/Boston50/Epsilon) Subject: Building a tree recursively 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_noforeach 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