Expected END found B1 Validation Configuration

Question

This is probably something simple but I can't see the answer.

I am using b1 usability package and trying to set validation configuration on a field to provide the value the query spits out. I've run the query in query generator in SAP B1 with no issues (give me '5') however when I use the same code in the B1 validation configuration it gives me the error "Error: Expected END found"

Code is below;

@STORE1 = SQL(
SELECT TOP 1
SUM(
CASE WHEN (T4.[ItmsGrpNam] LIKE '%%Ind%%' OR T3.ItemName LIKE '%%Industrial%%' OR T2.ItemName LIKE '%%Industrial%%') THEN 1.0
WHEN (T4.[ItmsGrpNam] LIKE '%%ENV%%' OR T2.[itemName] LIKE '%%ENV%%') THEN 0.5
WHEN (T2.ItemName LIKE '%%WATER%%') THEN 0.5
WHEN (T3.[ItemName] LIKE '%%Comm%%' OR T4.[ItmsGrpNam] LIKE '%%Comm%%') THEN 0.50
WHEN (T3.[ItemName] LIKE '%%IRON%%' OR T4.[ItmsGrpNam] LIKE '%%IRON%%' OR T2.[itemName] LIKE '%%IB42310%%') THEN 1.00 
END
)
FROM OCRD T0 
INNER JOIN [dbo].[@SWA_CT_SUBSCR]  T1 ON T1.U_CardCode = T0.CardCode
INNER JOIN OINS T2 ON T0.[CardCode] = T2.[customer]
INNER JOIN OITM T3 ON T2.[itemCode] = T3.[ItemCode]
INNER JOIN OITB T4 ON T3.ItmsGrpCod = T4.ItmsGrpCod 
WHERE T2.[status] = 'A' AND  
T2.[itemName] NOT LIKE '%%Printer%%' AND  
T2.[itemName] NOT LIKE '%%Label%%' AND  
T3.[ItemName] NOT LIKE '%%IRON%%' AND  
T3.[ItemName] NOT LIKE '%%FILTER%%' AND 
T2.[itemCode] NOT LIKE '%%PYO%%' AND
T2.[itemCode] NOT LIKE '%%ONYX%%' AND
T2.[itemCode] NOT LIKE '%%TOPAZ%%' AND
T2.[itemCode] NOT LIKE '%%NUTEK OZONE%%' AND
T2.[itemCode] NOT LIKE '%%OLS OZONE%%' AND
T0.CardCode = $[$4.0.0]
);
IF(@STORE1 <> '')
BEGIN
Set($[$SWA_CT_BT.0.NUMBER]|@STORE1);
END

Any thoughts as to why it would be giving me this error?

Answer 1

I was encountering the Same errors even when I had simplified the Query,

Try Putting the SQL(all your sql text) on a single line, for example:

@STORE1 = 
SQL(SELECT Cardname FROM OCRD T0 WHERE T0.CardCode = $[$4.0.0]); 
IF(@STORE1 <> '')
BEGIN
Set($[$16.0.0]|@STORE1)
END