Invalid identifier error while running SQL query in ireport designer

Question

I am running following SQL query in iReport designer query explorer:

SELECT 
A.DOC_TYPE AS DOC_TYPE,(CASE
WHEN EXTRACT(MONTH FROM  A.UPLOAD_DT) = 01 THEN 'January'
WHEN EXTRACT(MONTH FROM  A.UPLOAD_DT) = 02 THEN 'February'
WHEN EXTRACT(MONTH FROM  A.UPLOAD_DT) = 03 THEN 'March'
WHEN EXTRACT(MONTH FROM  A.UPLOAD_DT) = 04 THEN 'April'
WHEN EXTRACT(MONTH FROM  A.UPLOAD_DT) = 05 THEN 'May'
WHEN EXTRACT(MONTH FROM  A.UPLOAD_DT) = 06 THEN 'June'
WHEN EXTRACT(MONTH FROM  A.UPLOAD_DT) = 07 THEN 'July'
WHEN EXTRACT(MONTH FROM  A.UPLOAD_DT) = 08 THEN 'Augst'
WHEN EXTRACT(MONTH FROM  A.UPLOAD_DT) = 09 THEN 'September'
WHEN EXTRACT(MONTH FROM  A.UPLOAD_DT) = 10 THEN 'October'
WHEN EXTRACT(MONTH FROM  A.UPLOAD_DT) = 11 THEN 'November'
WHEN EXTRACT(MONTH FROM  A.UPLOAD_DT) = 12 THEN 'December'
END) AS M, EXTRACT(YEAR FROM  A.UPLOAD_DT) AS Y,SUM(A.TOTAL) AS TOTAL
FROM
(select DOC_TYPE, UPLOAD_DT, count(DOC_TYPE) as TOTAL
from CASE_UPLOADS
where UPLOAD_DT >= '01-JAN-15'
AND UPLOAD_DT <= '31-MAR-15'
AND DOC_TYPE = 'QR7'
OR DOC_TYPE = 'SAR7'
group by DOC_TYPE,UPLOAD_DT
order by DOC_TYPE) A
GROUP BY DOC_TYPEY,M,TO_CHAR (A.UPLOAD_DT, 'MM')
ORDER BY DOC_TYPE,Y, TO_CHAR (A.UPLOAD_DT, 'MM')

Its throwing me following error:

ORA:00904:"M":Invalid Identifier

Can someone please tell me why am I getting this error? In which way I can resolve it?

I worked on other query similar to this and it works fine. Here is the query:

SELECT CASE WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 01 THEN 'January'
     WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 02 THEN 'February'
     WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 03 THEN 'March'
     WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 04 THEN 'April'
     WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 05 THEN 'May'
     WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 06 THEN 'June'
     WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 07 THEN 'July'
     WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 08 THEN 'Augst'
     WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 09 THEN 'September'
     WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 10 THEN 'October'
     WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 11 THEN 'November'
     WHEN EXTRACT(MONTH FROM app_pgm_req_dt) = 12 THEN 'December'
   END AS MONTH,
   EXTRACT(YEAR FROM app_pgm_req_dt) AS YEAR,
   COUNT(*) 
  FROM app_pgm_choice 
 GROUP BY YEAR, MONTH, TO_CHAR (app_pgm_req_dt, 'MM')
 ORDER BY YEAR, TO_CHAR (app_pgm_req_dt, 'MM');

Answer 1

Presumably, you are getting the error because a column alias defined in the SELECT is generally not allowed in the group by:

GROUP BY DOC_TYPEY, M, TO_CHAR(A.UPLOAD_DT, 'MM')
--------------------^

This depends on the database.

Instead, you can use this expression:

GROUP BY DOC_TYPEY, EXTRACT(MONTH FROM  A.UPLOAD_DT), EXTRACT(YEAR FROM  A.UPLOAD_DT)

because you also need the year in the group by.

As for why the other version worked, I imagine you might have a column named month in app_pgm_choice. Or, perhaps it is connected to a database that does support aliases in the group by clause.

By the way, is there a reason you are not using to_char(a.upload_dt, 'Mmm')?