ProTech-Online.com

IBM DB2 SQL code example to pull Quantus customers primary contact number.

CREATE FUNCTION CHAD_CNC( C_BRANCH VARCHAR(8), C_OBJECTNO BIGINT )
    RETURNS VARCHAR(48)
    SPECIFIC CNC
    LANGUAGE SQL
    READS SQL DATA
    NO EXTERNAL ACTION
    RETURN
       SELECT QUANTUS.CONTACT_NUMBER.PRIMARYNUMBER
       FROM QUANTUS.OLINK
    INNER JOIN QUANTUS.CONTACT_NUMBER
       ON QUANTUS.OLINK.BRANCHB = QUANTUS.CONTACT_NUMBER.BRANCH
       AND QUANTUS.OLINK.OBJECTNOB = QUANTUS.CONTACT_NUMBER.OBJECTNO
    WHERE QUANTUS.OLINK.BRANCHA = C_BRANCH
       AND QUANTUS.OLINK.OBJECTNOA = C_OBJECTNO
       AND QUANTUS.CONTACT_NUMBER.TYPE = 'C'
       AND QUANTUS.OLINK.DTOBSOLETE = '9999-01-01'
       AND QUANTUS.OLINK.TYPE = 201

       AND QUANTUS.OLINK.DESCOBJECTNO > 0