ProTech-Online.com

IBM DB2 SQL code example to pull Quantus customer linked files.

CREATE PROCEDURE CHAD_TEST_CUSTOMERS_FILES ( )
SPECIFIC CHAD_CUSTOMERS_FILES
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
    DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
    SELECT QUANTUS.CUSTOMER.CUSTOMERACCT,
       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.CUSTOMER
       INNER JOIN QUANTUS.OLINK
           ON QUANTUS.CUSTOMER.BRANCH = QUANTUS.OLINK.BRANCHB
           AND QUANTUS.CUSTOMER.OBJECTNO = QUANTUS.OLINK.OBJECTNOB
       INNER JOIN QUANTUS.FILE_REVISION
           ON QUANTUS.OLINK.BRANCHA = QUANTUS.FILE_REVISION.BRANCH
           AND QUANTUS.OLINK.OBJECTNOA = QUANTUS.FILE_REVISION.FILEOBJECTNO
       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
       INNER JOIN QUANTUS.EMPLOYEE
           ON QUANTUS.FILE_REVISION.APPRBYBRANCH = QUANTUS.EMPLOYEE.BRANCH
           AND QUANTUS.FILE_REVISION.APPRBYOBJECTNO = QUANTUS.EMPLOYEE.OBJECTNO
    WHERE QUANTUS.EMPLOYEE.OBJECTNO <> 10016
           AND QUANTUS.FILE_REVISION.DELETESTATUS = 'N'
           AND (QUANTUS.FILE_REVISION.STATUS = 'A' OR QUANTUS.FILE_REVISION.STATUS = 'O')
           AND QUANTUS.FILE_REVISION.DTCREATED < '2012-05-31';
    OPEN cursor1;

END