Incorrect Syntax near =

Question

Hi

I have below code. I am getting error Incorrect Syntax near = . I am using in Sap b1 Hana Query

DECLARE ColumnNames nvarchar(100);
Declare SQLQuery nvarchar(100);

SELECT ColumnNames = STRING_AGG(QUOTENAME(T2."U_A_M"), ',')
FROM (SELECT DISTINCT (T2."U_A_M")
            FROM Opch T0 inner join Pch1 T1 on T0."Docentry" = T1."DocEntry"
	inner join Oitm T2 on T1."ItemCode" = T2."ItemCode"
	where T2.U_A_M <> 'NA');

-- Step 2: Construct the dynamic SQL query
SET SQLQuery 'SELECT itemcode, ' + @cols + ' from
            (
                SELECT T1."ItemCode",T1."LineTotal", T2."U_A_M"
    FROM Opch T0 inner join Pch1 T1 on T0."Docentry" = T1."DocEntry"
	inner join Oitm T2 on T1."ItemCode" = T2."ItemCode"
	where T2.U_A_M <> ''NA''
           ) x
            pivot
            (
                 sum(linetotal)
                for U_A_M in (' + @cols + ')
            ) p '


execute(SQLQuery)

Thanks

Muhammad Imran Ansari · Answer

Hello Ramco, The error you're encountering is related to incorrect variable usage in your SAP HANA SQL query. SAP HANA has some differences from other SQL systems like SQL Server when it comes to variables and dynamic SQL. Here are the key issues: Variable assignment should be done without using = in the SELECT statement for variables. Use INTO instead. Dynamic SQL string assignment should use := instead of = . Here is the corrected version of your query for SAP HANA: DECLARE ColumnNames NVARCHAR(1000); -- Increase size for large list DECLARE SQLQuery NVARCHAR(1000); -- Increase size for large SQL queries -- Step 1: Get the column names dynamically SELECT STRING_AGG(QUOTENAME(T2."U_A_M"), ', ') INTO ColumnNames FROM ( SELECT DISTINCT T2."U_A_M" FROM Opch T0 INNER JOIN Pch1 T1 ON T0."Docentry" = T1."DocEntry" INNER JOIN Oitm T2 ON T1."ItemCode" = T2."ItemCode" WHERE T2."U_A_M" 'NA' ) AS cols; -- Step 2: Construct the dynamic SQL query SQLQuery := 'SELECT ItemCode, ' || ColumnNames || ' FROM ( SELECT T1."ItemCode", T1."LineTotal", T2."U_A_M" FROM Opch T0 INNER JOIN Pch1 T1 ON T0."Docentry" = T1."DocEntry" INNER JOIN Oitm T2 ON T1."ItemCode" = T2."ItemCode" WHERE T2."U_A_M" ''NA'' ) x PIVOT ( SUM(LineTotal) FOR U_A_M IN (' || ColumnNames || ') ) p'; -- Step 3: Execute the dynamic SQL query EXECUTE IMMEDIATE :SQLQuery; Good Luck!

Sophia Carter · Answer

The error "Incorrect Syntax near =" you are encountering can be pinpointed to your SQL query syntax. In SQL, when assigning values in a SELECT statement, you cannot use the = symbol directly to assign aliases to columns within the query itself. Let's focus on the problematic part of your SQL code: SELECT ColumnNames = STRING_AGG(QUOTENAME(T2."U_A_M"), ',') To correct this, you can modify it to adhere to the correct SQL syntax. You should remove the assignment using = and modify the query like this: SELECT STRING_AGG(QUOTENAME(T2."U_A_M"), ',') AS ColumnNames By making this adjustment, you are correctly assigning the value returned by the STRING_AGG function to the ColumnNames alias. Remember, in SQL, when you want to assign an alias to the result of a function or column, you should use AS followed by the alias name. This will help you avoid the "Incorrect Syntax near =" error. For the rest of your SQL script, ensure to follow the correct syntax patterns for query construction and concatenation to maintain a well-formed dynamic SQL query in SAP B1 HANA. If you make these necessary adjustments, your query should execute without encountering the "Incorrect Syntax near =" issue. If you encounter any further challenges or if you need additional clarification, feel free to ask!