Hi All,

I need help on coding a recursive loop for searching values from the database. My requirement is that I am sending the parent Product Id & Name as parameters to a function which is using these parameters in SQL Search query to find Sub-Products under the Parent Product Id & Name. This function will be called repeatedly so that I can find all the sub-products till the 'nth' level until there are no sub-products left. Each time the function will return the Product id & Name for the sub-product which will be used to call the function again.


Any help on this would be gr8 !!!

Im not so sure you need a recursive function for this. I do this all the time using SQL joins. Cant you get the records in one query? If not, why not?

I read your post again thinking I mis-read it. If you do things the way you are suggesting there are going to be alot of queries being executed. IMO, to many.

Wind is your friend
Matt

Can you help me on the join query ?

presently I am using the following queries:
select Distinct ID,PartNum,PName
from Product,TRLNK246
where
Product.ID in (select Object2 from TRLNK246 where Product= 1) and Product.Status = 'Active'

select Distinct ID,PartNum,PName
from Product,TRLNK246
where
Product.ID in (select Object2 from TRLNK246 where Product= 17) and Product.Status = 'Active'

select Distinct ID,PartNum,PName
from Product,TRLNK246
where
Product.ID in (select Object2 from TRLNK246 where Product= 11) and Product.Status = 'Active'

---------------------------
Here in this case :
Product Object2
1 15
1 16
1 17
17 11
17 12
17 13
11 5
11 6
11 7
---------------------
The above structure is for the Link table TRLNK246 which is a child table of the main Product Table cantaining the distinct Product IDs.

I am fetching the child table products contained in Object2 related to the main Products in Field Product.

I basically thought of a recursive function which will generate the sub-products.