error in String_Agg

Ramco Ramco

When i write below query i get error in String_Agg. If i use DMax or Distinct then it works

Select T1."transType",

WHEN T1."TransType" = '46' THEN (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") 
from JDT1 T1

Thanks

3 Replies

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

Sangeetha S1y ago

SELECT T1."transType",
       CASE WHEN T1."TransType" = '46' THEN (
           SELECT GROUP_CONCAT(A0."LocCode" SEPARATOR ',')
           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"
       ) END AS LocCodes
FROM JDT1 T1;

Database Version: Ensure your database version supports STRING_AGG. For example, in SQL Server, STRING_AGG was introduced in SQL Server 2017.

Alternative Functions: If STRING_AGG is not supported, you can use alternative functions like GROUP_CONCAT in MySQL

Sangeetha S1y ago

Syntax and Compatibility: Ensure that STRING_AGG is supported by your SQL database. For example, STRING_AGG is available in PostgreSQL and SQL Server, but not in MySQL. If you're using MySQL, you might need to use GROUP_CONCAT instead.
Correct Usage: Make sure the syntax is correct. Here's a revised version of your query:

SELECT T1."transType",
       CASE WHEN T1."TransType" = '46' THEN (
           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"
       ) END AS LocCodes
FROM JDT1 T1;

Eliana Blake1y ago
The error you are encountering with `STRING_AGG` in your query is likely due to a syntax issue. The `STRING_AGG` function is not always directly supported in all SQL databases, so you might need to use an alternative method or function depending on the database system you are using. Since you mentioned that it works with `DMax` or `Distinct`, it implies that `STRING_AGG` might not be supported in your current SQL environment.

If you are using SQL Server, for example, the `STRING_AGG` function was introduced in SQL Server 2017 and onwards. If you are using an older version of SQL Server or a different database system that does not support `STRING_AGG`, you can achieve a similar result using `DMax` or `Distinct` as you mentioned.

Here is an example using `DMax` as an alternative to `STRING_AGG` in SQL Server:
```
SELECT T1."transType",
    CASE
        WHEN T1."TransType" = '46' THEN (
            SELECT STUFF((SELECT ',' + 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"
                          FOR XML PATH('')), 1, 1, '')
    END
FROM JDT1 T1
```
In this query, `STUFF` and `FOR XML PATH('')` are used to concatenate the values from the subquery into a comma-separated list.

Make sure to adjust the query based on the specific SQL dialect you are using to achieve the desired result. If you provide more details about the database system you are working with, I can offer more tailored guidance.
0