title: Passing parameters into subquery raising invalid identifier
body: <p>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 <code>FOR XML PATH('')</code>. But in Oracle 12c that's a different story :</p>
  
  <p>Table definition </p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE HCF (
  ID NUMBER,
  HCF_DATE DATE,
  HCF_TYPE_1 NUMBER,
  HCF_TYPE_2 NUMBER)
  </code></pre>
  
  <p>Sample data</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The query I'm using</p>
  
  <pre class="lang-sql prettyprint-override"><code>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) &gt; 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
  </code></pre>
  
  <p>The error</p>
  
  <pre><code>ORA-00904: "a"."HCF_TYPE_2" :  invalid identifier
  </code></pre>
  
  <p>The desired output</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I think that the problem is that fields from the original query are not visible in the sub query of the <code>CROSS APPLY</code> sub query.</p>
  
  <p>PS : I have tried other ways described in <a href="https://oracle-base.com/articles/misc/string-aggregation-techniques" rel="nofollow noreferrer">this article</a> but I fail for multiple reasons like for <code>LISTAGG</code> it exceeds the xK number of characters. And I don't have sufficient privileges to create a function and <code>XMLAGG</code> might bring down your Oracle instance.</p>
  
  <p><strong>Update</strong>
  Oracle version is : <code>Oracle Database 12c Enterprise Edition Release 12.1.0.2.0</code></p>