我有一个存储过程,我想只选择行到一个临时表,其中输入参数日期小于等于字段日期。但我一直得到错误“变量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 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
谢谢你,沃尔特
1条答案
按热度按时间wztqucjr1#
不幸的是,您不能在
DECLARE GLOBAL TEMPORARY TABLE
语句中使用任何变量。使用2个不同的语句,如下例所示。