My query is using LISTAGG and it is showing an error 00937. 00000 - "not a single-group group function" I have gone through many stackoverflow questions similar to this error and those answers are using some subquery for resolving the issue. I don't want to write a subquery in this query
SELECT DISTINCT AMD.UNID AS APPLICATION_REF_ID,
LISTAGG(LOC.PLT,',') WITHIN GROUP (ORDER BY LOC.PLT ) AS MYLOC
FROM TAB1 AMD
LEFT JOIN TAB2 PER
ON NVL (AMD.PERMITNEWID, AMD.PERMITID) = PER.UNID
LEFT JOIN TAB3 LOC
ON NVL(AMD.PERMITNEWID, AMD.PERMITID) = LOC.PERMITID
WHERE NVL (AMD.PERMITNEWID, AMD.PERMITID) = PER.UNID
you have a missing group by, you don't need distinct() if you are grouping by UNID
SELECT AMD.UNID AS APPLICATION_REF_ID,
LISTAGG(LOC.PLT,',') WITHIN GROUP (ORDER BY LOC.PLT ) AS MYLOC
FROM TAB1 AMD
LEFT JOIN TAB2 PER
ON NVL (AMD.PERMITNEWID, AMD.PERMITID) = PER.UNID
LEFT JOIN TAB3 LOC
ON NVL(AMD.PERMITNEWID, AMD.PERMITID) = LOC.PERMITID
WHERE NVL (AMD.PERMITNEWID, AMD.PERMITID) = PER.UNID
GROUP BY AMD.UNID
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加