ProTech-Online.com

IBM DB2 SQL code example to pull Quantus service request comments.

CREATE FUNCTION CHAD_TEST_SRO_COMMENT( C_BRANCH VARCHAR(8), C_OBJECTNO BIGINT )
    RETURNS VARCHAR(32672)
    SPECIFIC TEST_SRO_COMMENT
    LANGUAGE SQL
    READS SQL DATA
    NO EXTERNAL ACTION
BEGIN ATOMIC
    DECLARE tmpString VARCHAR(32672) DEFAULT '';
    DECLARE tmpString2 VARCHAR(32672) DEFAULT '';
    loop1: FOR ROW AS
           SELECT
           QUANTUS.SERVICE_COMMENT.COMMENTNO,
           RTRIM(CHAD_TEST_EMPLOYEE_STD(QUANTUS.SERVICE_COMMENT.COMMENTBYBRANCH, QUANTUS.SERVICE_COMMENT.COMMENTBYOBJECTNO)) AS COMMENTBY,
           QUANTUS.SERVICE_COMMENT.DTCOMMENT,
           QUANTUS.SERVICE_COMMENT.TMCOMMENT,
           QUANTUS.SERVICE_COMMENT.STATUS,
           CHAD_TEST_TEXT_DATA(QUANTUS.SERVICE_COMMENT.BRANCH, QUANTUS.SERVICE_COMMENT.COMMENTOBJECTNO) AS COMMENT
       FROM QUANTUS.SERVICE_COMMENT
       WHERE C_BRANCH = QUANTUS.SERVICE_COMMENT.BRANCH
           AND C_OBJECTNO = QUANTUS.SERVICE_COMMENT.SERVICEOBJECTNO
       ORDER BY COMMENTNO DESC
    DO
           SET tmpString2 = (CASE ROW.STATUS
               WHEN 'A' THEN 'APPROVED'
               WHEN 'C' THEN 'CLOSED'
               WHEN 'Y' THEN 'DELAYED'
               WHEN 'D' THEN 'DELIVERED'
               WHEN 'I' THEN 'INCOMING'
               WHEN 'N' THEN 'NEW'
               WHEN 'F' THEN 'READY_FOR_APPROVAL'
               WHEN 'O' THEN 'REOPENED'
               WHEN 'R' THEN 'RESOLVED'
               WHEN 'S' THEN 'STARTED'
               WHEN 'V' THEN 'VERIFIED'
               ELSE ''
               END);
       SET tmpString = tmpString || 'Comment #' || ROW.COMMENTNO || ' by ' || ROW.COMMENTBY || ' on '
           || CHAR(DTCOMMENT,USA) || ' at ' || CHAR(TMCOMMENT,USA) || ' (' || tmpString2 || ')' || X'0A' || X'0A';
       SET tmpString = tmpString || ROW.COMMENT;
       SET tmpString = tmpString || X'0A' || X'0A';
       SET tmpString2 = '';
    END FOR;
    RETURN tmpString;

END