ProTech-Online.com

IBM DB2 SQL code example to search Quantus for part information.

CREATE PROCEDURE TEST_SEARCH_INVENTORY
(
    IN @BRANCH1 VARCHAR(32672),
    IN @BRANCH2 VARCHAR(32672),
    IN @BRANCH3 VARCHAR(32672),
    IN @ITEMNUM VARCHAR(32672),
    IN @DESCRIPTION VARCHAR(32672),
    IN @GROUP VARCHAR(32672),
    IN @SUBGROUP VARCHAR(32672),
    IN @COMPONENT VARCHAR(32672),
    IN @GLGROUP VARCHAR(32672),
    IN @PROVINCE VARCHAR(32672),
    IN @COUNTRY VARCHAR(32672),
    IN @TARRIFNUM VARCHAR(32672),
    IN @NAFTAAPPROVED CHAR,
    IN @STATUS1 CHAR,
    IN @STATUS2 CHAR,
    IN @STATUS3 CHAR,
    IN @STATUS4 CHAR,
    IN @STATUS5 CHAR
)
SPECIFIC SEARCH_INVENTORY
DYNAMIC RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
       SELECT
           QUANTUS.INVENTORY.BRANCH,
           QUANTUS.INVENTORY.ITEMNUM,
           (RTRIM(QUANTUS.INVENTORY.DESCRIPTION1) CONCAT QUANTUS.INVENTORY.DESCRIPTION2) AS DESCRIPTION,
           QUANTUS.INVENTORY.ITEMGROUP,
           QUANTUS.INVENTORY.ITEMSUBGROUP,
           QUANTUS.INVENTORY.SUBGROUP2,
           QUANTUS.INVENTORY.GLDEFAULTCODE,
           QUANTUS.INVENTORY.PROVOFORIGIN,
           QUANTUS.INVENTORY.COUNTRYOFORIGIN,
           QUANTUS.INVENTORY.EXPORTTARIFFNUM,
           QUANTUS.INVENTORY.EXPORTAPPRTYPE,
           QUANTUS.INVENTORY.STATUS,
           QUANTUS.INVENTORY.STANDARDCOST,
            QUANTUS.INVENTORY_VALUE.LASTCOST,
            CASE WHEN QUANTUS.INVENTORY_VALUE.ONHANDQTY > 0 THEN
                QUANTUS.INVENTORY_VALUE.ACTUALCOST/QUANTUS.INVENTORY_VALUE.ONHANDQTY ELSE 0
               END AS AVG_COSTS
       FROM
           QUANTUS.INVENTORY
           LEFT OUTER JOIN QUANTUS.INVENTORY_VALUE ON
           QUANTUS.INVENTORY.BRANCH = QUANTUS.INVENTORY_VALUE.BRANCH
               AND QUANTUS.INVENTORY.ITEMNUM = QUANTUS.INVENTORY_VALUE.ITEMNUM
       WHERE
           LTRIM(UPPER(QUANTUS.INVENTORY.BRANCH)) IN (@BRANCH1, @BRANCH2, @BRANCH3)
           AND LTRIM(UPPER(QUANTUS.INVENTORY.ITEMNUM)) LIKE UPPER(@ITEMNUM)
           AND UPPER((QUANTUS.INVENTORY.DESCRIPTION1 CONCAT QUANTUS.INVENTORY.DESCRIPTION2)) LIKE UPPER(@DESCRIPTION)
           AND RTRIM(UPPER(QUANTUS.INVENTORY.ITEMGROUP)) LIKE UPPER(@GROUP)
           AND RTRIM(UPPER(QUANTUS.INVENTORY.ITEMSUBGROUP)) LIKE UPPER(@SUBGROUP)
           AND RTRIM(UPPER(QUANTUS.INVENTORY.SUBGROUP2)) LIKE UPPER(@COMPONENT)
           AND RTRIM(UPPER(QUANTUS.INVENTORY.GLDEFAULTCODE)) LIKE UPPER(@GLGROUP)
           AND RTRIM(UPPER(QUANTUS.INVENTORY.PROVOFORIGIN)) LIKE UPPER(@PROVINCE)
           AND RTRIM(UPPER(QUANTUS.INVENTORY.COUNTRYOFORIGIN)) LIKE UPPER(@COUNTRY)
           AND RTRIM(UPPER(QUANTUS.INVENTORY.EXPORTTARIFFNUM)) LIKE UPPER(@TARRIFNUM)
           AND RTRIM(UPPER(QUANTUS.INVENTORY.EXPORTAPPRTYPE)) LIKE UPPER(@NAFTAAPPROVED)
           AND QUANTUS.INVENTORY.STATUS IN (@STATUS1, @STATUS2, @STATUS3, @STATUS4, @STATUS5)
       ORDER BY QUANTUS.INVENTORY.BRANCH, QUANTUS.INVENTORY.ITEMNUM;
    -- Cursor left open for client application
    OPEN cursor1;

END P1