ProTech-Online.com

IBM DB2 SQL code example to pull Quantus data for a Wal-Mart GIS report.

CREATE PROCEDURE TEST_WALMART_GIS ( IN @STARTDATE Date, IN @ENDDATE Date )
SPECIFIC WALMART_GIS
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
    DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR
    SELECT
       QUANTUS.ORDER_LINE.DTSHIPPED AS SHIP_DATE,
       RTRIM(QUANTUS.CUSTOMER.MAILCOUNTRY) AS MARKET,
       LTRIM(QUANTUS.ORDER_LINE.ALTITEMNUM) AS WALMART_ITEM_NUMBER,
       LTRIM(QUANTUS.ORDER_LINE.ITEMNUM) AS VENDOR_STOCK_NUMBER,
       RTRIM(QUANTUS.SALES_ORDER.CUSTPONUM) AS PO_DETAIL,
       QUANTUS.SALES_ORDER.DTORDER AS PO_DATE,
       QUANTUS.ORDER_LINE.SHIPQTY AS SHIPPED_QUANTITY,
       QUANTUS.ORDER_LINE.UNITPRICE AS ITEM_USD_COST
      FROM QUANTUS.SALES_ORDER, QUANTUS.ORDER_LINE, QUANTUS.CUSTOMER
      WHERE QUANTUS.SALES_ORDER.BRANCH = QUANTUS.ORDER_LINE.BRANCH
            AND QUANTUS.SALES_ORDER.ORDERNUM = QUANTUS.ORDER_LINE.ORDERNUM
            AND QUANTUS.SALES_ORDER.RELEASE = QUANTUS.ORDER_LINE.RELEASE
            AND QUANTUS.SALES_ORDER.SHIPBRANCH = QUANTUS.CUSTOMER.BRANCH
            AND QUANTUS.SALES_ORDER.SHIPACCT = QUANTUS.CUSTOMER.CUSTOMERACCT
            AND QUANTUS.ORDER_LINE.SOURCE = 'S'
            AND LTRIM(QUANTUS.ORDER_LINE.BRANCH) = '02'
            AND LTRIM(QUANTUS.CUSTOMER.BILLINGACCT) = 'WALMARTU'
            AND QUANTUS.ORDER_LINE.DTSHIPPED BETWEEN @STARTDATE AND @ENDDATE 
            AND QUANTUS.ORDER_LINE.OPPARENTUNIQUE = 0
      ORDER BY QUANTUS.SALES_ORDER.CUSTPONUM ASC, QUANTUS.ORDER_LINE.LINESEQNO ASC;
    OPEN c1;

END