Syntax error near Distinct

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!

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Emily Foster1y ago
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!
0