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 :

Table definition 

```sql
CREATE TABLE HCF (
ID NUMBER,
HCF_DATE DATE,
HCF_TYPE_1 NUMBER,
HCF_TYPE_2 NUMBER)

```

Sample data

```
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

```sql
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

```

The error

```
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](https://oracle-base.com/articles/misc/string-aggregation-techniques) 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.

**Update**
Oracle version is : `Oracle Database 12c Enterprise Edition Release 12.1.0.2.0`