Distint Location

Question

Hi

I have below code and i want to get distinct location using stuff_agg in sql . Below is the 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 
where T1."TransId" = 1897692

Thanks

sasikala s · Answer

SELECT T1."TransId", ( SELECT STRING_AGG(DISTINCT A0."LocCode", ',') WITHIN GROUP (ORDER BY 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 WHERE T1."TransId" = 1897692; DISTINCT A0."LocCode" : Inside the subquery, we've added the DISTINCT keyword before A0."LocCode" . This ensures that only unique location codes from the PCH1 table are considered before being aggregated.

Daniel Wright · Answer

Based on the code snippet you provided, it looks like you are aiming to retrieve distinct locations using the `STRING_AGG` function in SQL. The `STRING_AGG` function is used for string concatenation, which in this case is aggregating the "LocCode" values. However, to retrieve distinct locations, you may want to modify the query slightly. To get distinct locations using `STRING_AGG`, you can add the `DISTINCT` clause inside the `STRING_AGG` function to ensure unique location values are concatenated. Here's a modified version of your SQL query: 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 WHERE T1."TransId" = 1897692 By adding `DISTINCT` within `STRING_AGG`, you ensure that only unique "LocCode" values are concatenated for each row, providing you with distinct locations based on the criteria specified in your query. This adjustment should help you achieve your goal of retrieving distinct locations. If you have any further questions or need more clarification, feel free to ask!

Distint Location

Answers (2)