Syntax error near Distinct

Question

Hi I am getting above error SELECT T1."TransId",( Select STRING_AGG (Distinct(A0."LocCode"),',') from PCH1 A0 inner join OPCH A1 on A0."DocEntry" = A1."DocEntry" and A1."BPLId" = T1."BPLId" inner join VPM2 A2 on A2."DocNum" = T1."CreatedBy" and A0."DocEntry" = A2."DocEntry") As "Location" from JDT1 T1 Thanks

Emily Foster · Answer

The syntax error near "Distinct" in your SQL query stems from the misplaced use of the DISTINCT keyword within the STRING_AGG function. In SQL, the DISTINCT keyword is used within the SELECT statement to eliminate duplicate rows from the result set, not within functions like STRING_AGG. To correct the syntax error, you should remove the DISTINCT keyword from inside the STRING_AGG function. Here is the revised SQL query: SELECT T1."TransId", (SELECT STRING_AGG(A0."LocCode", ',') FROM PCH1 A0 INNER JOIN OPCH A1 ON A0."DocEntry" = A1."DocEntry" AND A1."BPLId" = T1."BPLId" INNER JOIN VPM2 A2 ON A2."DocNum" = T1."CreatedBy" AND A0."DocEntry" = A2."DocEntry" ) AS "Location" FROM JDT1 T1 By removing DISTINCT from within the STRING_AGG function, the query should now execute without any syntax errors. If you were aiming to have distinct values concatenated within STRING_AGG, you may want to ensure the distinct values are obtained from the underlying query rather than within the function itself to avoid syntax errors. Let me know if you need further clarification or assistance with SQL queries!

Syntax error near Distinct

Answers (1)