DB2存储过程报错：变量P_DATE未定义或不可用

问题原因 #

你遇到的"变量P_DATE未定义或不可用"错误，本质是DB2的语法限制：DECLARE GLOBAL TEMPORARY TABLE的AS (SELECT ...)子查询无法直接引用存储过程的输入参数。临时表的定义在过程编译阶段就会被解析，此时参数还未完成实例化，导致参数无法被识别。

修正方案 #

改用先创建临时表结构，再通过INSERT INTO插入数据的方式，就能在INSERT的SELECT语句中正常引用P_DATE参数。修正后的代码如下：

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 (
        "orderNumber" VARCHAR(20),
        "orderNumberKey" VARCHAR(40),
        "orderSfIntegrationId" VARCHAR(100),
        "orderCustomerNumber" VARCHAR(20),
        "orderGenerationNumber" INTEGER,
        "orderLineSequenceNumber" INTEGER,
        "orderLineItemType" VARCHAR(20),
        "orderLineShipToNumber" VARCHAR(20),
        "orderLineRepNumber" VARCHAR(45),
        "orderLineWarehouseNumber" VARCHAR(20),
        "orderLineCMEPONumber" VARCHAR(20),
        "orderLineType" VARCHAR(20),
        "orderLineStatus" VARCHAR(20),
        "orderLineHoldCode" VARCHAR(10),
        "orderLineInvoiceDate" VARCHAR(10),
        "orderLineItemNumber" VARCHAR(30),
        "orderLineItemDescription" VARCHAR(100),
        "orderLineItemMPN" VARCHAR(30),
        "orderLineItemNonFileSKU" VARCHAR(20),
        "orderLineQuantityOrdered" DECIMAL(18,2),
        "orderLineQuantityShipped" DECIMAL(18,2),
        "orderLineQuantityBackordered" DECIMAL(18,2),
        "orderLineQuantityCode" VARCHAR(10),
        "orderLineUnitOfMeasure" VARCHAR(10),
        "orderLineTotalSell" DECIMAL(18,2),
        "orderLineItemAverageCost" DECIMAL(18,2),
        "orderLineTotalCost" FLOAT,
        "orderLineProfit" FLOAT,
        "status" VARCHAR(20)
    ) ON COMMIT PRESERVE ROWS;

    -- 插入数据并正常引用P_DATE参数
    INSERT INTO SESSION.FACTORDERLINEINFO
    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;
END

额外优化提示 #

• 临时表字段的类型和长度需与SELECT结果集完全匹配，上述代码中的类型为常规场景推测，请根据实际表结构调整。
• 若OAIPDT是YYYYMMDD格式的数字类型，可改用DATE(TO_CHAR(o2.OAIPDT, 'FM00000000'), 'YYYYMMDD')转换日期，比字符串拼接更高效。

内容的提问来源于stack exchange，提问作者Walter Farber