ProTech-Online.com

IBM DB2 SQL code example to pull Quantus assembly material codes.

CREATE PROCEDURE TEST_ASSEMBLY_MAT_CODES (
    IN @CODE VARCHAR(1000)
)
SPECIFIC ASSEMBLY_MAT_CODES
DYNAMIC RESULT SETS 1
LANGUAGE SQL
READS SQL DATA
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT
       LTRIM(QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM) AS ITEMNUM,
       TRIM(QUANTUS.ASSEMBLY_OPTION.BRANCH) AS BRANCH,
       QUANTUS.ASSEMBLY_OPTION.OPCODE,
       CONCAT(QUANTUS.INVENTORY.DESCRIPTION1, QUANTUS.INVENTORY.DESCRIPTION2) AS DESCRIPTION,
       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
       INNER 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)) LIKE UPPER(@CODE)
           AND QUANTUS.ASSEMBLY_OPTION.TYPE = 'W'
    ORDER BY QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM, QUANTUS.ASSEMBLY_OPTION.BRANCH;
    -- Cursor left open for client application
    OPEN cursor1;

END P1