Re: Please Advice

In article <338B3A7D.9C0@estina.mal.hp.com>, Kwan Weng Hong <kuanwh@estina.mal.hp.com> wrote: >Hi everybody ... Please excuse me if I make any error as this is the >first time I use Informix. > >I use a function that contains a CURSOR to retrieve some records. Inside >the function I call back the same function when certain condition are met >(recursive function). > >Question : >- Will the CURSOR be overwrite if I call the function ? >- Can Informix handle a function that contain CURSOR recursively ? > >For example : >I have 2 tables - LNK and PHL >LNK - store the relationship between a follower and a leader >PHL - store a number indicating which one is at higher level. > The number for leader must > than follower >A follower can have many leader and a leader can have many follower. > >Table : LNK Table : PHL >follower leader Device Number >---------------------- ----------------------- >B A A 4 >C A B 1 >D C C 3 >E C D 2 >F H E 1 >G D F 1 > G 1 > H 2 > >This is the result I want to achieve (PHL table) when given the data >in LNK table. > >Please advice ... Thanks I'll comment on how to achieve your result rather than on your question on cursors. (This is a typical Bill of Materials problem) The approach I use (in 4GL) uses a temporary table in which items are progressively inserted during the process of building the parent-child chain. 1. Work with one leader at a time (foreach) 2. Insert the leader into a 'clean' temporary table of structure icounter smallint levelno smallint item setting icounter = 1 and levelno = 0 3. Start a while loop on the temporary table within which you do the following (initialize counter = 1) 3.1 select the item, levelno whose icounter matches counter. If you find none, you're done. 3.2 Retrieve all followers of this item (foreach), inserting them into same temporary table with - levelno = leader's levelno + 1 (i.e next level) - icounter progressively increasing 3.3 Add 1 to counter and go to 3.1 4. At the end of the while loop, you should have rows in your temp table whose max(levelno) links to the original leader. You could replace the temporary table with a memory array, but you will have to be sure of the maximum possible children and grand- children. If you are after a parent-child tree, processing will be slightly different during the tempoary table insert of children (you may have to 'push' rows; you'd probably also be better off having another field in the temp table for itemchild) HTH. ----------------------- Rudy Fernandes GIC, Kuwait OL 7.20UC4, 4GL 6.04UC1 -----------------------