IBM DB2 SQL code example to search Quantus BOM assembly formulas.

CREATE PROCEDURE TEST_SEARCH_FORMULAS ( IN I_TESTSTRING VARCHAR(32672))
SPECIFIC SEARCH_FORMULAS
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TABLE (
       INVBRANCH VARCHAR(8),
       PARENTITEMNUM VARCHAR(32),
       OPTIONNAME VARCHAR(24),
       OPSEQUENCE VARCHAR(8),
       PARENTSEQ VARCHAR(8),
       OPCODE VARCHAR(4),
       OPERREQD VARCHAR(1),
       ADJORDERAMT VARCHAR(1),
       ADJINVENTORY VARCHAR(1),
       CREATESUBASSB VARCHAR(1),
       ITEMQTY DOUBLE,
       FORMULANAME VARCHAR(12))
       ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
    BEGIN ATOMIC
       DECLARE tmpString VARCHAR(32672) DEFAULT '';
       DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR
           SELECT *
           FROM SESSION.TEMP_TABLE;
       loop1: FOR ROW1 AS
           SELECT QUANTUS.ASSEMBLY_OPTION.INVBRANCH, QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM,
             QUANTUS.ASSEMBLY_OPTION.OPTIONNAME, QUANTUS.ASSEMBLY_OPTION.OPSEQUENCE,
               QUANTUS.ASSEMBLY_OPTION.PARENTSEQ, QUANTUS.ASSEMBLY_OPTION.OPCODE,
               QUANTUS.ASSEMBLY_OPTION.OPERREQD, QUANTUS.ASSEMBLY_OPTION.ADJORDERAMT,
               QUANTUS.ASSEMBLY_OPTION.ADJINVENTORY, QUANTUS.ASSEMBLY_OPTION.CREATESUBASSB,
               QUANTUS.ASSEMBLY_OPTION.ITEMQTY, QUANTUS.CALC_FORMULA.FORMULANAME
           FROM QUANTUS.ASSEMBLY_OPTION JOIN QUANTUS.CALC_FORMULA ON QUANTUS.ASSEMBLY_OPTION.FORMULAITEMNUM = QUANTUS.CALC_FORMULA.FORMULANAME
           WHERE QUANTUS.CALC_FORMULA.UNIQUENO = 1
       DO
           SET tmpString = '';
           loop2: FOR ROW2 AS
               SELECT QUANTUS.CALC_FORMULA.FORMULANAME,
                  QUANTUS.CALC_FORMULA.UNIQUENO,
                  QUANTUS.CALC_FORMULA.CALCFORMULA
               FROM QUANTUS.CALC_FORMULA
               WHERE QUANTUS.CALC_FORMULA.FORMULANAME = ROW1.FORMULANAME
               ORDER BY UNIQUENO
           DO
               SET tmpString = tmpString CONCAT ROW2.CALCFORMULA;
           END FOR loop2;
        IF tmpString LIKE I_TESTSTRING
           THEN INSERT INTO SESSION.TEMP_TABLE VALUES (ROW1.INVBRANCH,ROW1.PARENTITEMNUM,ROW1.OPTIONNAME,ROW1.OPSEQUENCE,
               ROW1.PARENTSEQ,ROW1.OPCODE,ROW1.OPERREQD,ROW1.ADJORDERAMT,ROW1.ADJINVENTORY,ROW1.CREATESUBASSB,ROW1.ITEMQTY, ROW1.FORMULANAME);
       END IF;
       END FOR loop1;
       OPEN c1;
    END;

END