title: Oracle 11g LISTAGG function with multiple subquery
body: <p>I am working on Oracle 11g and I'm trying to write a query that will insert '?' for all missing months between 2 dates. This I'm able to achieve.
  However, now I want to collate all records of a particular column in a single record. I have used LISTAGG function to achieve this, however I'm getting an error "invalid identifier" for the column inside LISTAGG function.
  Here's my code:-</p>
  
  <p><code>Select facilitynumber, 
    LISTAGG(facilitystatus, '') WITHIN GROUP (ORDER BY null) "Profile"
    FROM (
  WITH allmonths AS (
      SELECT to_date(level, 'MM') AS allmnths FROM dual
     CONNECT BY LEVEL BETWEEN '01' AND '05'
  ), months AS (
  SELECT * FROM (
  SELECT ccds.facilitynumber,ccds.facilitystatus, dsub.filecreationdate as FacilityStatusDate, dsub.submissiondate,
  ROW_NUMBER() OVER (partition by ccds.facilitynumber,extract(month from dsub.submissiondate)  order by dsub.submissiondate DESC) r 
  FROM ccdssubmissions ccds INNER JOIN datasubmission dsub 
  ON ccds.datasubmissionid = dsub.datasubmissionid 
  INNER JOIN datasupplier dsup 
  ON dsub.datasupplierid = dsup.datasupplierid WHERE ccds.matchedcompanynumber = 'TEST9239'
  ORDER BY dsub.submissiondate DESC
  )  where r = 1)
  SELECT allmnths, CASE
        WHEN facilitystatus IS NULL
        THEN '?'
        ELSE facilitystatus
      END     AS "facilitystatus", submissiondate, facilitynumber
    FROM allmonths LEFT OUTER JOIN months
      ON extract(month from allmonths.allmnths) = extract(month from months.submissiondate) order by allmnths
      )  GROUP BY facilitynumber;</code></p>
  
  <p>I'm facing error in 2nd line itself. My subquery i.e. query starting from "WITH" is returning results as follows:-</p>
  
  <pre><code>ALLMNTHS| facilitystatus | submissiondate | facilitynumber 
  
  ---------    | ---------------    | ---------------    | ---------------    
  01-JAN-16    | U                  | 23-JAN-16          | FAC01              
  01-FEB-16    | ?                  | null               | null               
  01-MAR-16    | 1                  |05-MAR-16           | FAC01             
  </code></pre>
  
  <p>Now using the LISTAGG(facilitystatus, ''), I want my result to be -> </p>
  
  <pre><code>Profile|  facilitynumber
    U?1          |   FAC01   
  </code></pre>
  
  <p>But, Oracle is unable to recognise facilitystatus column and hence I'm unable to achieve desired result.</p>
  
  <p>Any help would be greatly appreciated.</p>
  
  <p>Thanks</p>