Re: Building a tree recursively

Doesn't David's approach (See below) assume that a sub-tree will never have a depth of more than 20 ? Of course if you could guarantee a depth of <=n you could tune your cursor array to an appropriate value. Anyway here's an approach I might at least examine if I had a similar problem. It is flat (non-recursive - which upsets me as I like recursion) and doesn't explicitly use any cursors but it does let the Engine take the strain. FUNCTION get_children(unit_no) DECLARE cur_count INTEGER, last_count INTEGER create temp table descend(child_no integer) INSERT INTO descend SELECT child_no FROM unit_link WHERE parent_no = unit_no SELECT count(*) INTO cur_count FROM descend LET last_count=cur_count - 1 WHILE cur_count > last_count INSERT INTO descend SELECT unit_link.child_no FROM unit_link,descend WHERE unit_link.parent_no=descend.child_no AND unit_link.child_no IS NOT IN ( SELECT child_no FROM descend ) LET last_count=cur_count SELECT count(*) INTO cur_count FROM descend END WHILE # Use descend table DROP TABLE descend END FUNCTION I haven't actually tried the above, but I'm sure you can see what I'm getting at. A varient could involve recording the level of the node in the tree in descend and only expanding the tree with nodes from the last level. Hope this helps Simon David Williams <djw@smooth1.demon.co.uk> wrote: > Create a "cursor mangement" library:- > DEFINE Cursors ARRAY[20] of > RECORD > used char(1) > END RECORD > > FUNCTION init_cursors - set array to all "N" - not unused > ... etc ... > Then use > DEFINE cursor_id integer > CALL prepare_cursor("Select...") RETURNING cursor_id > CALL open_cursor(cursor_id) > ... > CALL fetch_cursor(cursor_id) > ... > CALL close_cursor(cursor_id) > > A pain but you only have to write the library once.. > >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 --------------------------------------------------------------------------- DISCLAIMER: Any Opinions expressed above are, at best, only consistent with my state of mind at the time of posting. Simon Burrows, NW England simon@calyps0.demon.co.uk http://www.calyps0.demon.co.uk ---------------------------------------------------------------------------