Re: SQL Question

Paul Watson wrote: > You could merge get_hierachy and get_child into a single SPL, just > call the top level get_child with your starting point and a depth of > zero. Only one to maintain/document (??) Yup. Plus you could raise an exception on exceeding your levels limit, which would return a 'real' error to your app. I think you should be able to process more levels, but YMMV. > "Kapur, Rajesh" wrote: > >>Mark: Thank you very much.... I was able to find your SPL snippets and customize your code to my needs.... I include the details of what I have done, in case someone else can benefit from it... >> >>* The first stored procedure get_hierarchy() extracts the nodes with no parent. (I had to write a separate procedure because I had nulls in the parent_id field for the nodes with no parent. With a little work, we can probably get the same results with a single stored procedure.) >>* get_hierarchy calls get_child for each node with no parent. get_child recursively prints out all lower levels. >>* I included the description of each level. >>* I added a depth indicator and a check not to exceed 10 levels of recursion. >>* I included indentation of each level, based upon the depth level. >>* Table description, SPL code and sample output are included. Excellent! Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+ sending to informix-list