SAP B1查询返回“必须声明标量变量”错误
问题描述:
我正在尝试创建一个下拉菜单,它将限制物品采购员使用以下SQL代码查询结果。但是,我不断收到一个错误,指出我需要声明标量变量@ItemBuyer,尽管它明确声明并在代码中进行了设置。SAP B1查询返回“必须声明标量变量”错误
DECLARE @ItemBuyer VARCHAR(30)
SET @ItemBuyer= /* T3.OwnerCode */ '[%1]'
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SELECT T3.[DocNum] AS DocNumber, T3.[CardCode] AS
VendorCode, T3.[CardName] AS VendorName, T3.OwnerCode as BuyerID, T2.
[ItemCode] AS ItemNo, T2.[U_CPM_LegItemNo] AS LegacyItemNumber, T2.
[Dscription] AS ItemDescription, T2.[U_CPM_ConfDate] AS POConfirmDate,
T2.[OpenCreQty] AS CreditMemoAmount FROM [dbo].[OITG] T0 , [dbo].
[OITM] T1 INNER JOIN [dbo].[POR1] T2 ON T2.[ItemCode] = T1.
[ItemCode] INNER JOIN [dbo].[OPOR] T3 ON T3.[DocEntry] = T2.
[DocEntry] WHERE (T2.[OpenCreQty] > (0)) AND (T2.[U_CPM_ConfDate]
IS NULL ) and (T3.[OwnerCode] = @ItemBuyer)'
EXEC(@SQL)
我也尝试声明和设置变量象下面这样:
DECLARE @ItemBuyer VARCHAR(30) = /* T3.[OwnerCode] */ '[%0]'
但后来我得到一个错误,指出语法是错误的,甚至可以通过变量现在返回正确的值。我有点卡在这里。希望有人能帮助我。
感谢,
Krys
答
我会改写这样的查询:
/*SELECT FROM [dbo].[OPOR] P1*/
declare @ItemBuyer as int
/* WHERE */
set @ItemBuyer = /* P1.OwnerCode */ '[%0]'
SELECT T3.[DocNum] AS DocNumber
, T3.[CardCode] AS VendorCode
, T3.[CardName] AS VendorName
, T3.OwnerCode as BuyerID
, T2.[ItemCode] AS ItemNo
, T2.[U_CPM_LegItemNo] AS LegacyItemNumber
, T2.[Dscription] AS ItemDescription
, T2.[U_CPM_ConfDate] AS POConfirmDate
, T2.[OpenCreQty] AS CreditMemoAmount
FROM [dbo].[POR1] T2
INNER JOIN [dbo].[OPOR] T3 ON T3.[DocEntry] = T2.[DocEntry]
WHERE T2.[OpenCreQty] > 0
AND T2.[U_CPM_ConfDate] IS NULL
and T3.[OwnerCode] = @ItemBuyer
我仍然不能设法记住动态语法查询,所以我必须使用此链接每一次我想要做到这一点:http://www.clientsfirst-us.com/blog/sap/sap-business-one/user-defined-prompts-in-sap-business-one-queries/
请注意,我摆脱了几个表,你没有在你的使用或您的where
报表。这可能不会使查询速度产生很大的差异,但它只是使它更清晰。 (我还清理了格式以将其放入我最喜欢的样式中,但这不是必须的)
为什么使用动态sql? –
您正在使用动态查询,但您没有为动态查询提供价值。 – Hybridzz