# Recursive S.Proc

## Recommended Posts

Interbase

AAA Part's BOM (AAA uses 2 pieces AAA-1)

ITEMNO................CHILDITEMNO....Qty

AAA.........................AAA-1.....................2

AAA-1 Part's BOM

ITEMNO................CHILDITEMNO....Qty

AAA.........................AAB..........................2

AAA.........................AAC..........................3

I was expecting below result

ITEMNO....CHILDITEMNO...QTY

AAA.............AAA-1......................2

AAA1...........AAB..........................4

AAA1..........AAC...........................6

but i get below result

ITEMNO....CHILDITEMNO...QTY

AAA.............AAA-1......................2

AAA1...........AAB..........................2

AAA1..........AAC...........................3

My Recursive S.Proc like below

FOR SELECT

B.ITEMNO, B.CHILDITEMNO, SUM(B.QTY) QTY
FROM BOM B
WHERE B.ITEMNO= :"ITMNO"
GROUP BY B.ITEMNO,B.CHILDITEMNO
INTO :"ITEMNO",:"CHILDITEMNO", :"QTY"
DO
BEGIN
SUSPEND;
FOR SELECT

B1.ITEMNO,  B1.CHILDITEMNO,  SUM(B1.QTY) QTY
FROM BOMRECURSIVE(:"CHILDITEMNO") B1
GROUP BY B1.ITEMNO,B1.CHILDITEMNO
INTO  :"ITEMNO",:"CHILDITEMNO", :"QTY"
DO
BEGIN
SUSPEND;
END
END

Could some please correct my s.proc ?

Thank You

I'm just guessing that instead of Sum () you want to have the product AAA.QTY * AAA-1.QTY

Thank you so much Stano

if i change 2.select like below the query is very very very slow

SUSPEND;
FOR SELECT

B1.ITEMNO,  B1.CHILDITEMNO,  (B.QTY * B1.QTY) QTY  /* removed sum, added  b.qty  b1.qty
FROM BOMRECURSIVE(:"CHILDITEMNO") B1
JOIN BOM B ON B.CHILDITEMNO=B1.ITEMNO  /* added join */

INTO  :"ITEMNO",:"CHILDITEMNO", :"QTY"
DO
BEGIN
SUSPEND;
END
END

Edited by Henry Olive

11 hours ago, Henry Olive said:

if i change 2.select like below the query is very very very slow

Hi,

Something like

```CREATE TABLE BOM
( ITEMNO VARCHAR(5),
CHILDITEMNO VARCHAR(5),
QTY INTEGER
);
COMMIT;
INSERT INTO BOM VALUES('AAA','AAA-1',2);
INSERT INTO BOM VALUES('AAA-1','AAB',2);
INSERT INTO BOM VALUES('AAA-1','AAC',3);

SET TERM ^ ;
CREATE OR ALTER PROCEDURE GETBOM (
ITEM varchar(5),
QT integer )
RETURNS (
BOMITEM varchar(5),
BOMCHILD varchar(5),
BOMQTY integer )
AS
DECLARE VARIABLE Q INTEGER;
BEGIN
FOR SELECT ITEMNO,CHILDITEMNO,QTY*:QT FROM BOM
WHERE ITEMNO=:ITEM
INTO :BOMITEM,:BOMCHILD,:BOMQTY
DO begin
SUSPEND;
Q=:BOMQTY;

IF (NOT BOMCHILD IS NULL) THEN
BEGIN
FOR SELECT BOMITEM,BOMCHILD,BOMQTY FROM GETBOM(:BOMCHILD,:Q)
INTO :BOMITEM,:BOMCHILD,:BOMQTY
DO SUSPEND;
END
END
END^
SET TERM ; ^

SELECT BOMITEM, BOMCHILD, BOMQTY
FROM GETBOM ('AAA', 1); ```

Giving

Quote

AAA    AAA-1    2
AAA-1    AAB    4
AAA-1    AAC    6

P.S.  Note as I present my script (create table, populate, table etc.) , so it'is easy for anybody to test

Perfect
Thank you SO MUCH  Serge