ProTech-Online.com

IBM DB2 SQL code example to pull Quantus part drawings.

CREATE PROCEDURE CHAD_TEST_ITEM_FILES ( )
SPECIFIC CHAD_ITEM_FILES
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
    DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
    SELECT QUANTUS.INVENTORY.BRANCH,
       QUANTUS.INVENTORY.ITEMNUM,
       QUANTUS.FILE_REVISION.DTACTIVATED,
       QUANTUS.FILE_REVISION.TMACTIVATED,
       QUANTUS.FILE_REVISION.FILENAME,
       QUANTUS.FILE_REVISION.REVISIONNO,
       QUANTUS.TEXTDATA.DATA,
       QUANTUS.FILE_DATA.LOCATION
    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
           INNER JOIN QUANTUS.TEXTDATA
               ON QUANTUS.FILE_REVISION.BRANCH = QUANTUS.TEXTDATA.BRANCH
               AND QUANTUS.FILE_REVISION.DESCOBJECTNO = QUANTUS.TEXTDATA.OBJECTNO
           INNER JOIN QUANTUS.FILE_DATA
               ON QUANTUS.FILE_REVISION.DATABRANCH = QUANTUS.FILE_DATA.BRANCH
               AND QUANTUS.FILE_REVISION.DATAOBJECTNO = QUANTUS.FILE_DATA.OBJECTNO
    WHERE QUANTUS.FILE_REVISION.FILENAME NOT LIKE '%ENGINEER%'
           AND QUANTUS.INVENTORY.BRANCH NOT IN ('      PA','      SW','      T1','      T3','     MSP')
           AND QUANTUS.FILE_REVISION.DELETESTATUS = 'N'
           AND (QUANTUS.FILE_REVISION.STATUS = 'A' OR QUANTUS.FILE_REVISION.STATUS = 'O');
    OPEN cursor1;

END