Error Incorrect Syntax near =

Question

Hi

In below query i am getting error Incorrect Syntax near = when i am using Stuff statement. I am using this query in Sap B1 Hana version.

SELECT
    A."WhsCode",A."ItemCode",A."Description",A."Uom",A."Batch No.",A."Bin No"
    
From (
SELECT
    T1."WhsCode",T1."ItemCode",Max(T2."ItemName") As "Description",
    Max(T2."InvntryUom") As "Uom",T1."BatchNum" As "Batch No.",
    (Select 'BinNo' = STUFF(( Select ',' + (A3."BinCode") from  OBBQ A1  INNER JOIN OBTN A2 ON A1."SnBMDAbs" = A2."AbsEntry"
    inner join OBIN A3 on A3."WhsCode" = A1."WhsCode" and A3."AbsEntry" = A1."BinAbs"
    where A1."ItemCode" = T1."ItemCode" and A1."WhsCode" = T1."WhsCode" and A2."DistNumber" = T1."BatchNum" and A1."OnHandQty" > 0 for XML PATH('')),1,1,'')) As "Bin No", 


FROM IBT1 T1
LEFT JOIN OITM T2 ON T2."ItemCode" = T1."ItemCode" 
GROUP BY T1."WhsCode", T1."ItemCode", T1."BatchNum"
HAVING
    SUM(CASE WHEN T1."Direction" = 0 THEN T1."Quantity" ELSE 0 END) -
    SUM(CASE WHEN T1."Direction" = 1 THEN T1."Quantity" ELSE 0 END) > 0
) A

Thanks

Sophia Carter · Answer

The error Incorrect Syntax near = is commonly encountered when the SQL query contains incorrect syntax. In your specific case the issue stems from the use of the equal sign = within the SELECT clause of your subquery. The correct syntax for assignment in SQL is using the colon equals : not the equal sign = commonly used in other programming languages. When assigning a value in a SELECT statement you should use := instead of =. Here's an adjusted portion of your code snippet where the correct assignment syntax is used: (Select 'BinNo' := STUFF(( Select '' + (A3.BinCode) from OBBQ A1 INNER JOIN OBTN A2 ON A1.SnBMDAbs = A2.AbsEntry inner join OBIN A3 on A3.WhsCode = A1.WhsCode and A3.AbsEntry = A1.BinAbs where A1.ItemCode = T1.ItemCode and A1.WhsCode = T1.WhsCode and A2.DistNumber = T1.BatchNum and A1.OnHandQty > 0 for XML PATH(''))11'')) As Bin No By making this adjustment from = to := you should be able to correct the syntax error you were facing. Remember that different database systems might have specific ways of handling assignment within queries so it's essential to be mindful of these nuances. If you encounter any further issues or need additional clarification feel free to ask!