Error Incorrect Syntax near =

Hello Ramco,

Issue is inside 

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

incase above not work,

You can use STRING_AGG() in SAP Hana,

STRING_AGG(A3."BinCode", ',') AS "Bin No"

Hello Ramco,

The error Incorrect Syntax near = in your query is likely due to the incorrect usage of the STUFF function and the way the subquery is structured. In SAP HANA SQL, the syntax for string concatenation and the STUFF function might differ slightly

Here’s how you can fix your query:

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 STRING_AGG(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
        ) 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;

Good Luck!

For SAP HANA, you should use STRING_AGG or LISTAGG instead of STUFF with FOR XML PATH('')

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('')),1,1,'')) 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!