Passing parameters into subquery raising invalid identifier

Question

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

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

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

Answer 1

Finally I managed to ask the DBA to create a function that concatenates id values in CSV, which I personally think is the best approach to return a CLOB value.

The function :

create or replace FUNCTION concatenate_list (p_cursor IN  SYS_REFCURSOR)
  RETURN  CLOB
IS
  l_return  CLOB; 
  l_temp    CLOB;
BEGIN
  LOOP
    FETCH p_cursor
    INTO  l_temp;
    EXIT WHEN p_cursor%NOTFOUND;
    l_return := l_return || ',' || l_temp;
  END LOOP;
  RETURN LTRIM(l_return, ',');
END;

The query

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.*
  , concatenate_list(CURSOR(SELECT id FROM HCF WHERE HCF_DATE = a.HCF_DATE AND HCF_TYPE_1 = a.HCF_TYPE_1 AND HCF_TYPE_2 = a.HCF_TYPE_2)) AS CSV
FROM CTE_HCF a

Source

PS : If you don't need a CLOB then LISTAGG is your best choice.