DB2: Stored Proc Error for input variable "Variable not Defined or not Usable"

Question

I have a stored procedure where I would like to only select rows into a temp table where input parameter date is less than equal to a fields date. But I keep getting the error "Variable P_DATE not defined or not usable."

I have made sure that the datatypes I am using between the variable and the data field comparing to.

Any advice would be helpful. I have included my code below:

CREATE OR REPLACE PROCEDURE APFACTORDERLINEINFO 
(IN P_DATE DATE
)
SPECIFIC APFACTORDERLINEINFO
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
    
    DROP TABLE SESSION.FACTORDERLINEINFO IF EXISTS;
    DECLARE GLOBAL TEMPORARY TABLE FACTORDERLINEINFO AS (
        SELECT
            o.OBORNO AS "orderNumber",
            o.OBORNO||'_'||o2.OAENTD AS "orderNumberKey",
            o.OBORNO||'/'||LPAD(o.OBORGN,2,'0')||'/'||RIGHT(LEFT(o2.OAENTD,6),2)||'-'||RIGHT(o2.OAENTD,2)||'-'||LEFT(o2.OAENTD,4) AS "orderSfIntegrationId",
            o.OBCSNO AS "orderCustomerNumber",
            o.OBORGN AS "orderGenerationNumber",
            o.OBORSQ AS "orderLineSequenceNumber",
            CASE 
                WHEN trim(o.OBLITP) = 'I' THEN 'Item'
                WHEN trim(o.OBLITP) = 'M' THEN 'Message'
                WHEN trim(o.OBLITP) = 'C' THEN 'Charge'
                ELSE trim(o.OBLITP)
            END AS "orderLineItemType",
            ifnull(o.OBSHP#,'') AS "orderLineShipToNumber",
            CAST(o.OBSLNO AS VARCHAR(45)) AS "orderLineRepNumber",
            o.OBWHID AS "orderLineWarehouseNumber",
            o.OBOUPR AS "orderLineCMEPONumber",
            CASE 
                WHEN trim(o.OBORTP) = 'O' THEN 'Order'
                WHEN trim(o.OBORTP) = 'I' THEN 'Invoice'
                WHEN trim(o.OBORTP) = 'R' THEN 'Return'
                WHEN trim(o.OBORTP) = 'F' THEN 'Future'
                WHEN trim(o.OBORTP) = 'M' THEN 'Master'
                WHEN trim(o.OBORTP) = 'B' THEN 'Backorder'
                WHEN trim(o.OBORTP) = 'Q' THEN 'Quote'
                ELSE trim(o.OBORTP)
            END AS "orderLineType",
            '' AS "orderLineStatus", 
            o.OBOHLD AS "orderLineHoldCode",
            CASE 
                WHEN o2.OAIPDT = 0 THEN '0000-00-00'
                ELSE LEFT(o2.OAIPDT,4)||'-'||RIGHT(LEFT(o2.OAIPDT,6),2)||'-'||RIGHT(o2.OAIPDT,2) 
            END AS "orderLineInvoiceDate",
            trim(o.OBITNO) AS "orderLineItemNumber",
            CASE 
                WHEN i.IMITGL = 'NF' AND RIGHT(trim(o.OBITNO),3) IN ('999','777','888','-99','-88','-77') THEN o.OBITD1  
                WHEN i.IMITNO IS NULL THEN trim(o.OBITD1)||''||trim(o.OBITD2)
                ELSE trim(i.IMITD1)||''||trim(i.IMITD2)
            END AS "orderLineItemDescription",
            CASE 
                WHEN i.IMITGL = 'NF' AND RIGHT(trim(o.OBITNO),3) IN ('999','777','888','-99','-88','-77') THEN o.OBITD2 
                ELSE i.IMMFNO
            END AS "orderLineItemMPN",
            CASE 
                WHEN i.IMITGL = 'NF' AND RIGHT(trim(o.OBITNO),3) IN ('999','777','888','-99','-88','-77') THEN 'Non-File'
                ELSE 'SKU'
            END AS "orderLineItemNonFileSKU",           
            o.OBQTOR AS "orderLineQuantityOrdered",
            o.OBQTSH AS "orderLineQuantityShipped",
            o.OBBOQT AS "orderLineQuantityBackordered",
            o.OBUNMS AS "orderLineQuantityCode",
            o.OBUMCD AS "orderLineUnitOfMeasure",
            o.OBLNAM AS "orderLineTotalSell",
            o.OBCAVC AS "orderLineItemAverageCost",
            CAST(o.OBCAVC AS float) * CAST(o.OBQTSH AS float) AS "orderLineTotalCost",
            CAST(o.OBLNAM AS float) - (CAST(o.OBCAVC AS float) * CAST(o.OBQTSH AS float)) AS "orderLineProfit",
            'Historical' AS "status"
        FROM
            APLUS8FLV.HSDET o 
            JOIN APLUS8FLV.HSHED o2 ON o.OBHSSQ = o2.OAHSSQ AND o.OBORNO = o2.OAORNO 
            LEFT JOIN APLUS8FLV.ITMST i ON o.OBITNO = i.IMITNO
        WHERE
            1 = 1
            AND o.OBCONO = '2'
            AND trim(o.OBLITP) IN ('I','C')
            AND date(to_date(LEFT(o2.OAIPDT,4)||'-'||RIGHT(LEFT(o2.OAIPDT,6),2)||'-'||RIGHT(o2.OAIPDT,2),'YYYY-MM-DD')) >= P_DATE
    ) WITH DATA ON COMMIT PRESERVE ROWS;
END

Thanks, Walter

Answer 1

You can't use any variables in the DECLARE GLOBAL TEMPORARY TABLE statement unfortunately.
Use 2 distinct statements like in the example below.

CREATE OR REPLACE PROCEDURE TEST_TMP ()
BEGIN
  DECLARE V_TABSCHEMA VARCHAR (128) DEFAULT 'SYSCAT';

  DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST_TMP AS 
  (
    SELECT TABNAME
    FROM SYSCAT.TABLES
    --WHERE TABSCHEMA = V_TABSCHEMA
  ) 
  --WITH DATA 
  DEFINITION ONLY 
  WITH REPLACE ON COMMIT PRESERVE ROWS;

  INSERT INTO SESSION.TEST_TMP
  SELECT TABNAME
  FROM SYSCAT.TABLES
  WHERE TABSCHEMA = V_TABSCHEMA;

END@