ProTech-Online.com

IBM DB2 SQL code example to pull Quantus SRO customer references.

CREATE PROCEDURE CHAD_TEST_CUSTOMERS_SERVICE_REQUESTS_CUST_REFS ( )
SPECIFIC CHAD_CUSTOMERS_SERVICE_REQUESTS_CUST_REFS
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
    DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
    SELECT QUANTUS.CUSTOMER.CUSTOMERACCT,
       QUANTUS.SERVICE_REQUEST.SERVICEREQUESTNUM,
       QUANTUS.SERVICE_REQUEST.CUSTREFERNUM
    FROM QUANTUS.SERVICE_REQUEST
       INNER JOIN QUANTUS.CUSTOMER
           ON QUANTUS.SERVICE_REQUEST.ACCOUNTBRANCH = QUANTUS.CUSTOMER.BRANCH
           AND QUANTUS.SERVICE_REQUEST.ACCOUNTOBJECTNO = QUANTUS.CUSTOMER.OBJECTNO
    WHERE QUANTUS.SERVICE_REQUEST.ENTEREDBYOBJECTNO <> 10016
       AND QUANTUS.SERVICE_REQUEST.CUSTREFERNUM <> ''
       --AND QUANTUS.SERVICE_REQUEST.STATUS = 'C'
       AND QUANTUS.SERVICE_REQUEST.PROBLEMCODE <> 'GOLD'
       AND QUANTUS.SERVICE_REQUEST.PROBLEMCODE <> 'TEST'
       --AND QUANTUS.SERVICE_REQUEST.PROBLEMCODE <> 'LOGI'
       AND QUANTUS.SERVICE_REQUEST.PROBLEMCODE <> 'SHIP';
       --AND QUANTUS.SERVICE_REQUEST.DTCLOSED < '2011-05-01';
    OPEN cursor1;

END