ProTech-Online.com

IBM DB2 SQL code example to pull Quantus part and assembly descriptions.

CREATE PROCEDURE TEST_INVENTORY_FULL_DESC ()
SPECIFIC INVENTORY_FULL_DESC
DYNAMIC RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_ADDITION_DESC (
       BRANCH VARCHAR(8),
       ITEMNUM VARCHAR(32),
       DTCREATED DATE,
       MESSAGE VARCHAR(3000)
       )
       ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
    BEGIN ATOMIC
       DECLARE messageStr VARCHAR(32672) DEFAULT '';
       DECLARE fullmessageStr VARCHAR(32672) DEFAULT '';
       DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
           SELECT *
           FROM SESSION.TEMP_ADDITION_DESC;
       loop1: FOR ROW1 AS
           SELECT
               QUANTUS.INVENTORY.BRANCH,
               QUANTUS.INVENTORY.ITEMNUM,
               QUANTUS.INVENTORY.DTCREATED,
               QUANTUS.INVENTORY.DESCRIPTION1,
               QUANTUS.INVENTORY.DESCRIPTION2
           FROM QUANTUS.INVENTORY
       DO
           SET messageStr = '';
           loop2: FOR ROW2 AS
               SELECT
                  QUANTUS.ASSEMBLY_TEXT.MESSAGE
               FROM
                  QUANTUS.ASSEMBLY_TEXT
               WHERE
                  QUANTUS.ASSEMBLY_TEXT.BRANCH = ROW1.BRANCH
                  AND QUANTUS.ASSEMBLY_TEXT.ITEMNUM = ROW1.ITEMNUM
                  AND QUANTUS.ASSEMBLY_TEXT.OPERUNIQUENO = -1
               ORDER BY QUANTUS.ASSEMBLY_TEXT.UNIQUENO
           DO
               SET messageStr = messageStr CONCAT ROW2.MESSAGE;
           END FOR loop2;
           SET fullmessageStr = '';
           SET fullmessageStr = fullmessageStr CONCAT ROW1.DESCRIPTION1;
           SET fullmessageStr = fullmessageStr CONCAT ROW1.DESCRIPTION2;
           SET fullmessageStr = fullmessageStr CONCAT messageStr;
           INSERT INTO SESSION.TEMP_ADDITION_DESC
                      VALUES(
                          ROW1.BRANCH,
                          ROW1.ITEMNUM,
                          ROW1.DTCREATED,
                          fullmessageStr
                          );
       END FOR loop1;
       OPEN cursor1;
    END;

END P1