1

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?

Nick Jones
  • 93
  • 8
  • Just a thought but you are assigning @STORE1 to numeric values but then comparing it to a string - this might cause issues even if it is not the root cause of the error you are seeing. I would add to your CASE statement with "ELSE 0.0" and then have "IF(@STORE1 > 0.0)" – NickW Nov 11 '20 at 13:37
  • Hi Nick, i realised this shortly after posting. I have also added in the ELSE condition, still getting the same error message. – Nick Jones Nov 12 '20 at 00:29

1 Answers1

0

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
Praxiom
  • 578
  • 1
  • 8
  • 21
  • 1
    Hi sawsine, this is already a macro universal function. I believe the issue lies in my CASE statement up the top. I modified it to only have 1 WHEN condition and included ELSE 0.0. Now it gives me the error: Line 8: Error: Expected BEGIN found 0.0 – Nick Jones Nov 12 '20 at 22:37
  • Hi Nick I have edited my answer, hopefully it helps. – Praxiom Nov 13 '20 at 10:29
  • 1
    Hi sawsine, that worked!.. what an odd issue that it all has to be on one line. – Nick Jones Nov 16 '20 at 03:29