Informix SQL/4gl Nary Tree Explosion

> > As the title suggests, has anybody tackled such a beast. > that is, given a employee table > emp# manager_emp# other columns... > --- ----------- > > For a particular emp# (node) I want to find all employees/managers > under him whether directly or indirectly (all its children). > Has anybody done this under Informix? > Any pointers will be appreciated. > Thanks > > -- > Burzin N. Engineer |Tel. (310) 524 1800 | email: burzin@twinsun.com > Twin Sun Inc. |Fax. (310) 524 1818 | MIME OkaY! > Interesting enough to share. This is a standard Bill of Materials style explosion - which is an interesting beast under Informix. There are about 18 methods to deal with it. The most standard is a recursive procedure that walks the tree: Accept manager_emp# in some fashion WHILE whatever CALL recurse(manager_emp#) FUNCTION recurse(emp#) FOREACH emp# CALL (recurse(emp#)) END FUNCTION -------- The problem with this method in 4gl is that although you can recurse fine, the cursor can only be used once, the second pass through will give you an error about not opening an already open cursor. This method WILL work using a stored procedure (holler if you want an example), BUT, and it is a very big BUT, IF TWO PEOPLE TRY TO RUN THE STORED PROCEDURE AT ONE TIME YOUR ENGINE WILL CRASH. This is true on our engine 5.00.UC1. What we do, and it works, is: Accept manager_emp# in some fashion LET sel_stmt = "SELECT emp# FROM emp_table WHERE manager_emp# = ?" PREPARE s1 FROM sel_stmt DECLARE e_curs CURSOR FOR s1 LET read_idx = 1 LET fill_idx = 2 LET array[read_idx] = manager_emp# WHILE read_idx < fill_idx OPEN e_curs USING manager_emp# FOREACH e_curs INTO array[fill_idx].emp# LET fill_idx = fill_idx + 1 END FOREACH CLOSE e_curs END WHILE This is the basic algorithm, in this manner each emp# is checked to read its children which slide into the end of the array, when the read_idx gets down there the children will be exploded as well. When the two idx's = each other you have completed the read. In this manner you don't run into a problem with the cursor having to be opened twice. The only problem now is that we've lost the indentation, we have all the children, but not who they belong to. What we do for this is keep track of the master number as well, so the cursor is really: LET sel_stmt = "SELECT emp#, manager_emp# FROM emp_table WHERE manager_emp# = ?" We then post-process and sort everybody into the proper place. If you want the code we use holler, and I'll make a generic copy of it. Graeme noted that this was a really nasty solution, I beieve his words were 'Yeuuch', but it is a solution. Any other ideas would be welcome. I did quite a bit of research on this once before settling for this method, but am always willing to hear better ideas. happy new year. j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, BSMC Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ "Grant me the serenity to accept the things I cannot change, the courage to change the things I can, and the wisdom to hide the bodies of the people that I had to kill because they pissed me off." _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________