Oracle 11g LISTAGG function with multiple subquery

Question

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

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;

I'm facing error in 2nd line itself. My subquery i.e. query starting from "WITH" is returning results as follows:-

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             

Now using the LISTAGG(facilitystatus, ''), I want my result to be ->

Profile|  facilitynumber
  U?1          |   FAC01   

But, Oracle is unable to recognise facilitystatus column and hence I'm unable to achieve desired result.

Any help would be greatly appreciated.

Thanks

Answer 1

You are paying the price for what is a very poor practice, at least in Oracle.

When you declare a column name in double-quotes, such as "facilitystatus", it is recorded in the catalog exactly as written - in lowercase. When you refer to it in the outer query without the double-quotes, the name is automatically converted to upper-case (that's what Oracle does, I don't know about other DB products). So of course there is a mismatch.

There is absolutely no need for the double-quotes in the subquery. Remove them and see what happens. (Either the query will work, or you will get a different, unrelated error.) Good luck!

Don't ever use double-quoted names. They are used for the following reasons, none of which make sense (better to avoid the situation in the first place):

• Use specific capitalization (instead of case-insensitive - by default the names are entered in the catalog in all caps, and in your code you can use any capitalization)
• Embed spaces in names
• Use reserved words and keywords (like DATE and COLUMN) as column or table names