ProTech-Online.com

IBM DB2 SQL code example to pull Quantus customer contacts.

CREATE PROCEDURE CHAD_TEST_CUSTOMERS_CONTACTS ( )
SPECIFIC CHAD_CUSTOMERS_CONTACTS
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
    DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
    SELECT QUANTUS.CUSTOMER.CUSTOMERACCT,
       QUANTUS.CUSTOMER.NAME1,
       QUANTUS.CUSTOMER.MAILADDRESS1,
       QUANTUS.CUSTOMER.MAILADDRESS2,
       QUANTUS.CUSTOMER.MAILADDRESS3,
       QUANTUS.CUSTOMER.MAILADDRESS4,
       QUANTUS.CUSTOMER.MAILCITY,
       QUANTUS.CUSTOMER.MAILPROV,
       QUANTUS.TAILOR_CODES.DESCRIPTION AS MAILCOUNTRY,
       QUANTUS.CUSTOMER.MAILPOSTAL,
       QUANTUS.PERSON.GIVENNAME,
       QUANTUS.PERSON.LASTNAME,
       QUANTUS.PERSON.JOBTITLE,
       QUANTUS.PERSON.DEPARTMENT,
       CHAD_CNP(QUANTUS.OLINK.BRANCHB, QUANTUS.OLINK.OBJECTNOB) AS PRIMARY,
       CHAD_CNS(QUANTUS.OLINK.BRANCHB, QUANTUS.OLINK.OBJECTNOB) AS EXT,
       CHAD_CNF(QUANTUS.OLINK.BRANCHB, QUANTUS.OLINK.OBJECTNOB) AS FAX,
       CHAD_CNC(QUANTUS.OLINK.BRANCHB, QUANTUS.OLINK.OBJECTNOB) AS CELL,
       CHAD_CNE(QUANTUS.OLINK.BRANCHB, QUANTUS.OLINK.OBJECTNOB) AS EMAIL
    FROM QUANTUS.CUSTOMER
       INNER JOIN QUANTUS.OLINK
           ON QUANTUS.CUSTOMER.BRANCH = QUANTUS.OLINK.BRANCHA
           AND QUANTUS.CUSTOMER.OBJECTNO = QUANTUS.OLINK.OBJECTNOA
       INNER JOIN QUANTUS.PERSON
           ON QUANTUS.OLINK.BRANCHB = QUANTUS.PERSON.BRANCH
           AND QUANTUS.OLINK.OBJECTNOB = QUANTUS.PERSON.OBJECTNO
       LEFT OUTER JOIN QUANTUS.TAILOR_CODES
           ON QUANTUS.CUSTOMER.MAILCOUNTRY = QUANTUS.TAILOR_CODES.CODE
           AND QUANTUS.TAILOR_CODES.TYPE = 'O'
    WHERE QUANTUS.PERSON.DTDELETED = '9999-01-01'
       AND QUANTUS.PERSON.DTLASTMODIFIED BETWEEN '2008-08-08' AND '2012-05-31';
    OPEN cursor1;

END