Recursive S.Proc

[Henry Olive 5]

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

 

 

 

 

[Stano 143]

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

[Henry Olive 5]

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 January 23, 2021 by Henry Olive

[Guest]

11 hours ago, Henry Olive said:

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

https://edn.embarcadero.com/article/26293

[Serge_G 87]

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

[Henry Olive 5]

Perfect
Thank you SO MUCH  Serge