Question Re: SAP B1/SQL Server 2019 Query Error when entering Para... in Technology Q&A

SAP B1/SQL Server 2019 Query Error when entering Parameter

Earth_IT_Manager — Fri, 19 Sep 2025 04:43:20 GMT

If i use QUERY by not use '[%0]' No Problem

SELECT B.Bo_Pl_No AS [WMS No.], X.TransferNo AS [Transfer No.], CONVERT(date, B.Bo_CreateDate) AS [Date], P.Tp_ItemCode AS [Item No.], I.ItemName AS [Item Name], CAST(P.Tp_Qty AS decimal(19,6)) AS [Quantity], B.Bo_No AS [Box No.] FROM dbo.TB_Box AS B JOIN dbo.TB_TempPick AS P ON P.Tp_Box = B.Bo_No LEFT JOIN OITM AS I ON I.ItemCode = P.Tp_ItemCode LEFT JOIN ( SELECT COALESCE(CAST(T.U_wmsno AS nvarchar(50)), CAST(T.U_plateno AS nvarchar(50))) AS WMSNo, STRING_AGG(CAST(T.DocNum AS nvarchar(20)), ', ') WITHIN GROUP (ORDER BY T.DocNum) AS TransferNo FROM OWTR T JOIN NNM1 S ON S.Series = T.Series AND S.ObjectCode = '67' WHERE T.CANCELED = 'N' AND S.SeriesName LIKE 'ITS%' GROUP BY COALESCE(CAST(T.U_wmsno AS nvarchar(50)), CAST(T.U_plateno AS nvarchar(50))) ) AS X ON CAST(B.Bo_Pl_No AS nvarchar(50)) = X.WMSNo WHERE B.Bo_Active = 1 AND P.Tp_Active = 1 AND (ISNULL('','') = '' OR CONVERT(date, B.Bo_CreateDate) >= CONVERT(date, '')) AND (ISNULL('','') = '' OR CONVERT(date, B.Bo_CreateDate) <= CONVERT(date, '')) AND (ISNULL('','') = '' OR B.Bo_Pl_No = '') AND (ISNULL('','') = '' OR B.Bo_No = '') ORDER BY B.Bo_CreateDate, B.Bo_Pl_No, B.Bo_No, P.Tp_ItemCode;

But if i entering Parameter such as '[%0]' will be error immediately

Error 'Received Alert' (OAIB)

SELECT B.Bo_Pl_No AS [WMS No.], X.TransferNo AS [Transfer No.], CONVERT(date, B.Bo_CreateDate) AS [Date], P.Tp_ItemCode AS [Item No.], I.ItemName AS [Item Name], CAST(P.Tp_Qty AS decimal(19,6)) AS [Quantity], B.Bo_No AS [Box No.] FROM dbo.TB_Box AS B JOIN dbo.TB_TempPick AS P ON P.Tp_Box = B.Bo_No LEFT JOIN OITM AS I ON I.ItemCode = P.Tp_ItemCode LEFT JOIN ( SELECT COALESCE(CAST(T.U_wmsno AS nvarchar(50)), CAST(T.U_plateno AS nvarchar(50))) AS WMSNo, STRING_AGG(CAST(T.DocNum AS nvarchar(20)), ', ') WITHIN GROUP (ORDER BY T.DocNum) AS TransferNo FROM OWTR T JOIN NNM1 S ON S.Series = T.Series AND S.ObjectCode = '67' WHERE T.CANCELED = 'N' AND S.SeriesName LIKE 'ITS%' GROUP BY COALESCE(CAST(T.U_wmsno AS nvarchar(50)), CAST(T.U_plateno AS nvarchar(50))) ) AS X ON CAST(B.Bo_Pl_No AS nvarchar(50)) = X.WMSNo WHERE B.Bo_Active = 1 AND P.Tp_Active = 1 AND (ISNULL('[%0]','') = '' OR CONVERT(date, B.Bo_CreateDate) >= CONVERT(date, '[%0]')) AND (ISNULL('[%1]','') = '' OR CONVERT(date, B.Bo_CreateDate) <= CONVERT(date, '[%1]')) AND (ISNULL('[%2]','') = '' OR B.Bo_Pl_No = '[%2]') AND (ISNULL('[%3]','') = '' OR B.Bo_No = '[%3]') ORDER BY B.Bo_CreateDate, B.Bo_Pl_No, B.Bo_No, P.Tp_ItemCode;

Re: SAP B1/SQL Server 2019 Query Error when entering Para...

LoHa — Fri, 19 Sep 2025 04:39:12 GMT

Hi,

you can't us variables free in a query. SAP needs a field according to your variableit shows the fiedname and uses the field-kind to ask for it.

You can do it like

WHERE T0.DocDate = '[%0]'

If you need it in your case you can declare a Standard-SQL Variable at he beginning of your query (@DocDate) and use this later in the query like

/**SELECT FROM [OFPR] T0 **/ DECLARE @DocDateFrom AS Date /* WHERE */ SET @DocDateFrom = /* T0.F_RefDate */ '[%0]' Select * FROM ORDR WHERE DocDate = @DocDateFrom

Here some examples

/*Buchungsdatum*/ /**SELECT FROM [OFPR] T0 **/ DECLARE @DocDateFrom AS Date /* WHERE */ SET @DocDateFrom = /* T0.F_RefDate */ '[%0]' /**SELECT FROM [OFPR] T1 **/ DECLARE @DocDateTo AS Date /* WHERE */ SET @DocDateTo = /* T1.T_RefDate */ '[%1]' ----- /*Fälligkeitsdatum*/ /**SELECT FROM [OFPR] T0 **/ DECLARE @DocDateFrom AS Date /* WHERE */ SET @DocDateFrom = /* T0.F_DueDate */ '[%0]' /**SELECT FROM [OFPR] T1 **/ DECLARE @DocDateTo AS Date /* WHERE */ SET @DocDateTo = /* T1.T_DueDate */ '[%1]' ----- /*Belegdatum*/ /**SELECT FROM [OFPR] T0 **/ DECLARE @DocDateFrom AS Date /* WHERE */ SET @DocDateFrom = /* T0.F_TaxDate */ '[%0]' /**SELECT FROM [OFPR] T1 **/ DECLARE @DocDateTo AS Date /* WHERE */ SET @DocDateTo = /* T1.T_TaxDate */ '[%1]'/*Buchungsdatum*/ /**SELECT FROM [OFPR] T0 **/ DECLARE @DocDateFrom AS Date /* WHERE */ SET @DocDateFrom = /* T0.F_RefDate */ '[%0]' /**SELECT FROM [OFPR] T1 **/ DECLARE @DocDateTo AS Date /* WHERE */ SET @DocDateTo = /* T1.T_RefDate */ '[%1]' ----- /*Fälligkeitsdatum*/ /**SELECT FROM [OFPR] T0 **/ DECLARE @DocDateFrom AS Date /* WHERE */ SET @DocDateFrom = /* T0.F_DueDate */ '[%0]' /**SELECT FROM [OFPR] T1 **/ DECLARE @DocDateTo AS Date /* WHERE */ SET @DocDateTo = /* T1.T_DueDate */ '[%1]' ----- /*Belegdatum*/ /**SELECT FROM [OFPR] T0 **/ DECLARE @DocDateFrom AS Date /* WHERE */ SET @DocDateFrom = /* T0.F_TaxDate */ '[%0]' /**SELECT FROM [OFPR] T1 **/ DECLARE @DocDateTo AS Date /* WHERE */ SET @DocDateTo = /* T1.T_TaxDate */ '[%1]'

If you can't find a matching field for you requirements, at least you can setup a UDT and Name the Field as you like/need and take the needed Field-Type.

Sideeffect: When entering values in your UDT in the column, it will be shown as valid values when paramter is prompted

regards Lothar

Re: SAP B1/SQL Server 2019 Query Error when entering Para...

Earth_IT_Manager — Fri, 19 Sep 2025 06:45:39 GMT

In SSMS Work

Re: SAP B1/SQL Server 2019 Query Error when entering Para...

Earth_IT_Manager — Fri, 19 Sep 2025 06:48:53 GMT

In SSMS Work in SAP B1 Not Work

DECLARE @DateFrom DATE SET @DateFrom = '2025-07-01' DECLARE @DateTo DATE SET @DateTo ='2025-08-01' SELECT B.Bo_Pl_No AS [WMS No.], X.TransferNo AS [Transfer No.], CONVERT(date, B.Bo_CreateDate) AS [Date], P.Tp_ItemCode AS [Item No.], I.ItemName AS [Item Name], CAST(P.Tp_Qty AS decimal(19,6)) AS [Quantity], B.Bo_No AS [Box No.] FROM dbo.TB_Box B JOIN dbo.TB_TempPick P ON P.Tp_Box = B.Bo_No LEFT JOIN OITM I ON I.ItemCode = P.Tp_ItemCode LEFT JOIN ( SELECT COALESCE(CAST(T.U_wmsno AS nvarchar(50)), CAST(T.U_plateno AS nvarchar(50))) AS WMSNo, STRING_AGG(CAST(T.DocNum AS nvarchar(20)), ', ') WITHIN GROUP (ORDER BY T.DocNum) AS TransferNo FROM OWTR T JOIN NNM1 S ON S.Series = T.Series AND S.ObjectCode = '67' WHERE T.CANCELED = 'N' AND S.SeriesName LIKE 'ITS%' GROUP BY COALESCE(CAST(T.U_wmsno AS nvarchar(50)), CAST(T.U_plateno AS nvarchar(50))) ) X ON CAST(B.Bo_Pl_No AS nvarchar(50)) = X.WMSNo WHERE B.Bo_Active = 1 AND P.Tp_Active = 1 AND (@DateFrom IS NULL OR @DateTo IS NULL OR (B.Bo_CreateDate >= @DateFrom AND B.Bo_CreateDate < DATEADD(DAY,1,@DateTo))) ORDER BY B.Bo_CreateDate, B.Bo_Pl_No, B.Bo_No, P.Tp_ItemCode;

DECLARE @DateFrom DATE SET @DateFrom = '[%0]' DECLARE @DateTo DATE SET @DateTo ='[%1]' SELECT B.Bo_Pl_No AS [WMS No.], X.TransferNo AS [Transfer No.], CONVERT(date, B.Bo_CreateDate) AS [Date], P.Tp_ItemCode AS [Item No.], I.ItemName AS [Item Name], CAST(P.Tp_Qty AS decimal(19,6)) AS [Quantity], B.Bo_No AS [Box No.] FROM dbo.TB_Box B JOIN dbo.TB_TempPick P ON P.Tp_Box = B.Bo_No LEFT JOIN OITM I ON I.ItemCode = P.Tp_ItemCode LEFT JOIN ( SELECT COALESCE(CAST(T.U_wmsno AS nvarchar(50)), CAST(T.U_plateno AS nvarchar(50))) AS WMSNo, STRING_AGG(CAST(T.DocNum AS nvarchar(20)), ', ') WITHIN GROUP (ORDER BY T.DocNum) AS TransferNo FROM OWTR T JOIN NNM1 S ON S.Series = T.Series AND S.ObjectCode = '67' WHERE T.CANCELED = 'N' AND S.SeriesName LIKE 'ITS%' GROUP BY COALESCE(CAST(T.U_wmsno AS nvarchar(50)), CAST(T.U_plateno AS nvarchar(50))) ) X ON CAST(B.Bo_Pl_No AS nvarchar(50)) = X.WMSNo WHERE B.Bo_Active = 1 AND P.Tp_Active = 1 AND (@DateFrom IS NULL OR @DateTo IS NULL OR (B.Bo_CreateDate >= @DateFrom AND B.Bo_CreateDate < DATEADD(DAY,1,@DateTo))) ORDER BY B.Bo_CreateDate, B.Bo_Pl_No, B.Bo_No, P.Tp_ItemCode;

Re: SAP B1/SQL Server 2019 Query Error when entering Para...

LoHa — Fri, 19 Sep 2025 06:50:16 GMT

??? I can't understand you. It only works in SAPB1. Try sample ORDR query in query generator. It won't work in SMSS, because it is a special syntax for SAPB1

Re: SAP B1/SQL Server 2019 Query Error when entering Para...

LoHa — Fri, 19 Sep 2025 06:52:00 GMT

Try sample ORDR you need the /** and /* to get it running in SAP B1

Re: SAP B1/SQL Server 2019 Query Error when entering Para...

LoHa — Mon, 22 Sep 2025 04:57:35 GMT

So do you got it?

Then mark question as solved.

regards Lothar

Re: SAP B1/SQL Server 2019 Query Error when entering Para...

Earth_IT_Manager — Thu, 25 Sep 2025 05:58:14 GMT

I haven't tried it yet, I let the User enter it in the Query field instead.