Query with Parameter failing

[hemish]

Hi everyone,

I'm working on a complex query in SAP Business One that uses subqueries. However, I'm encountering an error when trying to use a dynamic parameter.

The query works perfectly when:

• The parameter is hardcoded.

The problem arises when:

• I try to make the parameter dynamic using the HANA syntax [%0].

Here's the working query (without the dynamic parameter):

SELECT
MFC."StartDate",
MFC."EndDate",
MFC."ForecastName",
T1."CardCode" AS "Business Partner Code",
T1."CardName" AS "Business Partner Name",
T2."U_Brand" AS "Brand",
MFC."ItemCode" AS "ItemCode",
MFC."Date" AS "Day Forecast",
MFC."ForecastQTY" AS "Forecast QTY",
MFC."SaleDate" AS "Invoice Date",
T3."Name" AS "Bill-to Country",
OTER."descript" AS "Territory",
T4."IndName" AS "Industry",
T5."GroupName" AS "Group",
T6."SlpName" AS "Sales Manager",
'' AS "Warehouse", -- leaving blank for now until we forecast by warehouse
T2."ItemName",
T1."Currency" AS "BP Currency", -- Business partner currency
MFC."Sales Value GBP" AS "Line Total GBP",
MFC."SoldQTY" AS "Invoice QTY"
FROM
(
SELECT
T0."U_BPCode" AS "CardCode",
T0."Code" AS "ForecastName",
T0."StartDate",
T0."EndDate",
T1."ItemCode",
T1."Date",
T1."Quantity" AS "ForecastQTY",
TSD."SaleDate",
TSD."QuantitySold" AS "SoldQTY",
TSD."Sales Value GBP"
FROM
DEV_SADP.OFCT T0
INNER JOIN DEV_SADP.FCT1 T1 ON T0."AbsID" = T1."AbsID"
LEFT JOIN
(
-- Sum up Sales Data
SELECT
SCT."CardCode",
SCT."ItemCode",
SCT."SaleDate",
SUM(SCT."QTYSold") AS "QuantitySold",
SUM(SCT."GBPSalesValue") AS "Sales Value GBP"
FROM
(
SELECT
T0."CardCode",
ADD_MONTHS(NEXT_DAY(LAST_DAY(T0."DocDate")), -1) AS "SaleDate",
T1."ItemCode",
SUM(T1."Quantity") AS "QTYSold",
SUM(T1."LineTotal") AS "GBPSalesValue"
FROM
OINV T0
INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
WHERE
T0."CANCELED" = 'N'
GROUP BY
T0."CardCode",
ADD_MONTHS(NEXT_DAY(LAST_DAY(T0."DocDate")), -1),
T1."ItemCode"
UNION ALL
SELECT
T0."CardCode",
ADD_MONTHS(NEXT_DAY(LAST_DAY(T0."DocDate")), -1) AS "SaleDate",
T1."ItemCode",
SUM(T1."Quantity") * -1 AS "QTYSold",
SUM(T1."LineTotal") * -1 AS "GBPSalesValue"
FROM
ORIN T0
INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry"
WHERE
T0."CANCELED" = 'N'
GROUP BY
T0."CardCode",
ADD_MONTHS(NEXT_DAY(LAST_DAY(T0."DocDate")), -1),
T1."ItemCode"
) AS SCT
GROUP BY
SCT."CardCode",
SCT."ItemCode",
SCT."SaleDate"
) TSD ON T0."U_BPCode" = TSD."CardCode" AND T1."Date" = TSD."SaleDate" AND T1."ItemCode" = TSD."ItemCode"
) MFC
LEFT JOIN "OCRD" T1 ON MFC."CardCode" = T1."CardCode"
LEFT JOIN "OITM" T2 ON MFC."ItemCode" = T2."ItemCode"
LEFT JOIN "OCRY" T3 ON T1."Country" = T3."Code"
LEFT JOIN "OTER" ON T1."Territory" = OTER."territryID"
LEFT JOIN "OOND" T4 ON T1."IndustryC" = T4."IndCode"
LEFT JOIN "OCRG" T5 ON T1."GroupCode" = T5."GroupCode"
LEFT JOIN "OSLP" T6 ON T1."SlpCode" = T6."SlpCode"
LEFT JOIN "OTER" TERPAR ON OTER."parent" = TERPAR."territryID";

Thanks

#sbo #query #parameter

[mgregur]

Hi,

I will randomly assume the parameter you need to use is CardCode (as you do not mention it anywhere). The solution to your problem would be to use the following syntax:

BEGIN
/* select from [dbo].[OCRD] T0 */
Declare CardCode nvarchar(50);
CardCode := /* T0."CardCode" */ '[%0]';


SELECT
MFC."StartDate",
MFC."EndDate",
MFC."ForecastName",
T1."CardCode" AS "Business Partner Code",
T1."CardName" AS "Business Partner Name",
T2."U_Brand" AS "Brand",
MFC."ItemCode" AS "ItemCode",
MFC."Date" AS "Day Forecast",
MFC."ForecastQTY" AS "Forecast QTY",
MFC."SaleDate" AS "Invoice Date",
T3."Name" AS "Bill-to Country",
OTER."descript" AS "Territory",
T4."IndName" AS "Industry",
T5."GroupName" AS "Group",
T6."SlpName" AS "Sales Manager",
'' AS "Warehouse", -- leaving blank for now until we forecast by warehouse
T2."ItemName",
T1."Currency" AS "BP Currency", -- Business partner currency
MFC."Sales Value GBP" AS "Line Total GBP",
MFC."SoldQTY" AS "Invoice QTY"
FROM
	(
	SELECT
	T0."U_BPCode" AS "CardCode",
	T0."Code" AS "ForecastName",
	T0."StartDate",
	T0."EndDate",
	T1."ItemCode",
	T1."Date",
	T1."Quantity" AS "ForecastQTY",
	TSD."SaleDate",
	TSD."QuantitySold" AS "SoldQTY",
	TSD."Sales Value GBP"
	FROM
	DEV_SADP.OFCT T0
	INNER JOIN DEV_SADP.FCT1 T1 ON T0."AbsID" = T1."AbsID" AND T0."U_BPCode" = CardCode
	LEFT JOIN
		(
		-- Sum up Sales Data
		SELECT
		SCT."CardCode",
		SCT."ItemCode",
		SCT."SaleDate",
		SUM(SCT."QTYSold") AS "QuantitySold",
		SUM(SCT."GBPSalesValue") AS "Sales Value GBP"
		FROM
			(
			SELECT
			T0."CardCode",
			ADD_MONTHS(NEXT_DAY(LAST_DAY(T0."DocDate")), -1) AS "SaleDate",
			T1."ItemCode",
			SUM(T1."Quantity") AS "QTYSold",
			SUM(T1."LineTotal") AS "GBPSalesValue"
			FROM
			OINV T0
			INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" AND T0."CardCode" = CardCode
			WHERE
			T0."CANCELED" = 'N' 
			GROUP BY
			T0."CardCode",
			ADD_MONTHS(NEXT_DAY(LAST_DAY(T0."DocDate")), -1),
			T1."ItemCode"
			UNION ALL
			SELECT
			T0."CardCode",
			ADD_MONTHS(NEXT_DAY(LAST_DAY(T0."DocDate")), -1) AS "SaleDate",
			T1."ItemCode",
			SUM(T1."Quantity") * -1 AS "QTYSold",
			SUM(T1."LineTotal") * -1 AS "GBPSalesValue"
			FROM
			ORIN T0
			INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry" AND T0."CardCode" = CardCode
			WHERE
			T0."CANCELED" = 'N'
			GROUP BY
			T0."CardCode",
			ADD_MONTHS(NEXT_DAY(LAST_DAY(T0."DocDate")), -1),
			T1."ItemCode"
			) AS SCT
		GROUP BY
		SCT."CardCode",
		SCT."ItemCode",
		SCT."SaleDate"
		) TSD ON T0."U_BPCode" = TSD."CardCode" AND T1."Date" = TSD."SaleDate" AND T1."ItemCode" = TSD."ItemCode"
	) MFC
LEFT JOIN "OCRD" T1 ON MFC."CardCode" = T1."CardCode"
LEFT JOIN "OITM" T2 ON MFC."ItemCode" = T2."ItemCode"
LEFT JOIN "OCRY" T3 ON T1."Country" = T3."Code"
LEFT JOIN "OTER" ON T1."Territory" = OTER."territryID"
LEFT JOIN "OOND" T4 ON T1."IndustryC" = T4."IndCode"
LEFT JOIN "OCRG" T5 ON T1."GroupCode" = T5."GroupCode"
LEFT JOIN "OSLP" T6 ON T1."SlpCode" = T6."SlpCode"
LEFT JOIN "OTER" TERPAR ON OTER."parent" = TERPAR."territryID";

END;

The key is to declare the parametar you need to use before the select itself, and then calling the parameter in your query. Please note that this was written without testing, as you have some UDFs and UDTs in there.

BR,

Matija