ProTech-Online.com

IBM DB2 SQL code example to search for sales order comments in Quantus.

CREATE PROCEDURE TEST_SEARCH_COMMENTS (
    IN @MESSAGE VARCHAR(32672))
SPECIFIC SEARCH_COMMENTS
DYNAMIC RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_ADD_INFO (
       SOURCE CHAR,
       BRANCH VARCHAR(8),
       ORDERNUM VARCHAR(8),
       RELEASE VARCHAR(8),
       TYPE CHAR,
       LINESEQNO INTEGER,
       ITEMNUM VARCHAR(32),
       MESSAGE VARCHAR(3000)
       )
       ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
    BEGIN ATOMIC
       DECLARE messageStr VARCHAR(32672) DEFAULT '';
       DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
           SELECT *
           FROM SESSION.TEMP_ADD_INFO;
       loop1: FOR ROW1 AS
           SELECT
               QUANTUS.ORDER_LINE.SOURCE,
               QUANTUS.ORDER_LINE.BRANCH,
               QUANTUS.ORDER_LINE.ORDERNUM,
               QUANTUS.ORDER_LINE.RELEASE,
               QUANTUS.ORDER_LINE.UNIQUENO,
               QUANTUS.ADD_INFO.TYPE,
               QUANTUS.ORDER_LINE.LINESEQNO,
               QUANTUS.ORDER_LINE.ITEMNUM
           FROM QUANTUS.ORDER_LINE
               INNER JOIN QUANTUS.ADD_INFO
                  ON QUANTUS.ADD_INFO.BRANCH = QUANTUS.ORDER_LINE.BRANCH
                  AND QUANTUS.ADD_INFO.ORDERNUM = QUANTUS.ORDER_LINE.ORDERNUM
                  AND QUANTUS.ADD_INFO.RELEASE = QUANTUS.ORDER_LINE.RELEASE
                  AND QUANTUS.ADD_INFO.SOUNIQUENO = QUANTUS.ORDER_LINE.UNIQUENO
           WHERE
               QUANTUS.ORDER_LINE.HISTORY <> 'Y'
               AND QUANTUS.ADD_INFO.TYPE IN ('M', 'T', 'G', 'C', 'B', 'U', 'S', 'W', 'L')
               AND QUANTUS.ADD_INFO.UNIQUENO = 1
           ORDER BY
               QUANTUS.ORDER_LINE.BRANCH DESC,
               QUANTUS.ORDER_LINE.ORDERNUM DESC,
               QUANTUS.ORDER_LINE.SOURCE DESC,
               QUANTUS.ORDER_LINE.RELEASE DESC,
               QUANTUS.ADD_INFO.TYPE DESC
       DO
           SET messageStr = '';
           loop2: FOR ROW2 AS
               SELECT
                  QUANTUS.ADD_INFO.MESSAGE
               FROM
                  QUANTUS.ADD_INFO
               WHERE
                  QUANTUS.ADD_INFO.BRANCH = ROW1.BRANCH
                  AND QUANTUS.ADD_INFO.SOURCE = ROW1.SOURCE
                  AND QUANTUS.ADD_INFO.ORDERNUM = ROW1.ORDERNUM
                  AND QUANTUS.ADD_INFO.RELEASE = ROW1.RELEASE
                  AND QUANTUS.ADD_INFO.TYPE = ROW1.TYPE
                  AND QUANTUS.ADD_INFO.SOUNIQUENO = ROW1.UNIQUENO
               ORDER BY UNIQUENO
           DO
               SET messageStr = messageStr CONCAT ROW2.MESSAGE;
           END FOR loop2;
           IF UPPER(messageStr) LIKE UPPER(@MESSAGE) THEN
               INSERT INTO SESSION.TEMP_ADD_INFO
                      VALUES(
                          ROW1.SOURCE,
                          ROW1.BRANCH,
                          ROW1.ORDERNUM,
                          ROW1.RELEASE,
                          ROW1.TYPE,
                          ROW1.LINESEQNO,
                          ROW1.ITEMNUM,
                          messageStr
                          );
           END IF;
       END FOR loop1;
       OPEN cursor1;
    END;

END P1