I have a query in which I want to group by some fields and aggregate the last field within a CSV string. If you come from SQL Server like me, you would use
FOR XML PATH(''). But in Oracle 12c that's a different story :
CREATE TABLE HCF ( ID NUMBER, HCF_DATE DATE, HCF_TYPE_1 NUMBER, HCF_TYPE_2 NUMBER)
ID HCF_DATE HCF_TYPE_1 HCF_TYPE_2 272 27/02/18 1 1 279 28/02/18 15 2 280 28/02/18 15 2 283 28/02/18 5 1
The query I'm using
WITH CTE_HCF AS ( SELECT HCF_DATE, HCF_TYPE_1, HCF_TYPE_2, COUNT(ID) FROM HCF GROUP BY HCF_DATE, HCF_TYPE_1, HCF_TYPE_2 HAVING COUNT(ID) > 0 ) SELECT a.*, b.* FROM CTE_HCF a CROSS APPLY ( SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(ORDRE_ID,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS ids FROM ( SELECT HCF_DATE, HCF_TYPE_1, HCF_TYPE_2, ID, ROW_NUMBER() OVER (PARTITION BY HCF_DATE ORDER BY HCF_TYPE_1, HCF_TYPE_2) AS curr, ROW_NUMBER() OVER (PARTITION BY HCF_DATE ORDER BY HCF_TYPE_1, HCF_TYPE_2) -1 AS prev FROM CTE_HCF WHERE HCF_DATE = a.HCF_DATE AND HCF_TYPE_1 = a.HCF_TYPE_1 AND HCF_TYPE_2 = a.HCF_TYPE_2 ) CONNECT BY prev = PRIOR curr AND HCF_DATE = PRIOR HCF_DATE AND HCF_TYPE_1 = PRIOR HCF_TYPE_1 AND HCF_TYPE_2 = PRIOR HCF_TYPE_2 START WITH curr = 1 ) b
ORA-00904: "a"."HCF_TYPE_2" : invalid identifier
The desired output
HCF_DATE HCF_TYPE_1 HCF_TYPE_2 IDS 27/02/18 1 1 272 28/02/18 15 2 279,280 28/02/18 5 1 283
I think that the problem is that fields from the original query are not visible in the sub query of the
CROSS APPLY sub query.
PS : I have tried other ways described in this article but I fail for multiple reasons like for
LISTAGG it exceeds the xK number of characters. And I don't have sufficient privileges to create a function and
XMLAGG might bring down your Oracle instance.
Oracle version is :
Oracle Database 12c Enterprise Edition Release 18.104.22.168.0