Hi!

I try to describe an expert problem.

BOM Table

Parent Child Qty
motor crank 1
motor bearing 6
motor chain 1
crank pipe 1
crank head 6
chain pod 72
head mat 4,5
head paint 3,4
head process 2,09





I need a SP, view or Trigger to do following
when I choose one ItemCode as Parent. The Result must be;

Child ID
---------- -----------
Motor 1
Cranck 2
pipe 3
head 4
mat 5
paint 6
process 7
Bearing 8
Chain 9
pod 10


To Solve the proble I tried a SP shown Below But RECURSIVE CALL of itself is not working


CREATE PROCEDURE PR_BOMVIEW (
BOM_ITEMCODE VARCHAR (40))
RETURNS (
ID INTEGER,
CHILD VARCHAR (40))
AS
DECLARE VARIABLE COUNTER double PRECISION;
BEGIN
/* Procedure body */
COUNTER = 1;
FOR
SELECT B.CHILD FROM BOM B WHERE B.PARENT = :BOM_ITEMCODE
INTO :CHILD
DO
BEGIN
ID = COUNTER;
COUNTER = COUNTER+1;
execute procedure PR_BOMVIEW(B.CHILD); /* THIS IS NOT WORKING */
SUSPEND;
END
END


Also I need to check the infinite loops for it. But, I have no idea...

Any help will be appreciated.

[Non-text portions of this message have been removed]