SAP B1/SQL Server 2019 Query Error when entering Parameter

[Earth_IT_Manager]

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;

[LoHa · Accepted solution]

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

[Earth_IT_Manager · Accepted solution]

In SSMS Work

[Earth_IT_Manager · Accepted solution]

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;

[LoHa · Accepted solution]

??? 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

[LoHa · Accepted solution]

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

[LoHa]

So do you got it?

Then mark question as solved.

regards Lothar

[Earth_IT_Manager]

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