ProTech-Online.com

IBM DB2 SQL code example to pull Quantus parts that don't have drawings.

CREATE PROCEDURE TEST_INVENTORY_WITHOUT_FILES (  )
SPECIFIC INVENTORY_WITHOUT_FILES
DYNAMIC RESULT SETS 1
LANGUAGE SQL
READS SQL DATA
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
       SELECT
           TRIM(QUANTUS.INVENTORY.BRANCH) AS BRANCH,
           LTRIM(QUANTUS.INVENTORY.ITEMNUM) AS ITEMNUM,
           RTRIM(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.INVENTORY
           LEFT OUTER JOIN QUANTUS.OLINK
               ON QUANTUS.INVENTORY.OBJECTNO = QUANTUS.OLINK.OBJECTNOB
               AND QUANTUS.INVENTORY.BRANCH = QUANTUS.OLINK.BRANCHB
           LEFT OUTER JOIN QUANTUS.FILE_REVISION
               ON QUANTUS.FILE_REVISION.FILEOBJECTNO = QUANTUS.OLINK.OBJECTNOA
               AND QUANTUS.FILE_REVISION.BRANCH = QUANTUS.OLINK.BRANCHA
       WHERE QUANTUS.FILE_REVISION.FILENAME IS NULL;
    -- Cursor left open for client application
    OPEN cursor1;

END P1