ProTech-Online.com

IBM DB2 SQL code example to pull Quantus workorders by material codes.

CREATE PROCEDURE TEST_ASSEMBLY_MAT_CODES_WO (
    IN @CODE VARCHAR(1000)
)
SPECIFIC ASSEMBLY_MAT_CODES_WO
DYNAMIC RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_ITEMNUM (ITEMNUM VARCHAR(32))
       ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
    BEGIN ATOMIC
       DECLARE cursor1 CURSOR WITH RETURN FOR
       SELECT
           LTRIM(QUANTUS.INVENTORY.ITEMNUM) AS ITEMNUM,
           TRIM(QUANTUS.INVENTORY.BRANCH) AS BRANCH,
           QUANTUS.ASSEMBLY_OPTION.OPCODE,
           CONCAT(QUANTUS.INVENTORY.DESCRIPTION1, QUANTUS.INVENTORY.DESCRIPTION2) AS DESCRIPTION,
           QUANTUS.ASSEMBLY_OPTION.TYPE,
           QUANTUS.INVENTORY.ITEMGROUP,
           QUANTUS.INVENTORY.ITEMSUBGROUP,
           QUANTUS.INVENTORY.SUBGROUP2,
           QUANTUS.INVENTORY.ITEMSORTGROUP1,
           QUANTUS.INVENTORY.ITEMSORTGROUP2,
           QUANTUS.INVENTORY.ITEMSORTGROUP3,
           QUANTUS.INVENTORY.LINETYPE,
           QUANTUS.INVENTORY.SOURCECODE,
           QUANTUS.INVENTORY.SOURCECODEPO
       FROM QUANTUS.ASSEMBLY_OPTION 
           RIGHT OUTER JOIN QUANTUS.INVENTORY ON
               QUANTUS.ASSEMBLY_OPTION.BRANCH = QUANTUS.INVENTORY.BRANCH
               AND QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM = QUANTUS.INVENTORY.ITEMNUM
       WHERE
           (UPPER(RTRIM(QUANTUS.ASSEMBLY_OPTION.OPCODE)) NOT LIKE UPPER(@CODE)
               OR QUANTUS.ASSEMBLY_OPTION.OPCODE IS NULL)
           AND (QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM NOT IN (SELECT * FROM SESSION.TEMP_ITEMNUM)
               OR QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM  IS NULL)
           AND (QUANTUS.ASSEMBLY_OPTION.TYPE = 'W'
               OR QUANTUS.ASSEMBLY_OPTION.TYPE IS NULL)
       ORDER BY QUANTUS.INVENTORY.ITEMNUM, QUANTUS.INVENTORY.BRANCH;
       INSERT INTO SESSION.TEMP_ITEMNUM (ITEMNUM)
           SELECT QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM
           FROM QUANTUS.ASSEMBLY_OPTION
           WHERE UPPER(RTRIM(QUANTUS.ASSEMBLY_OPTION.OPCODE)) LIKE UPPER(@CODE);
       -- Cursor left open for client application
       OPEN cursor1;
    END;

END P1