Give aliasing to the column arrears_in_days with double quotation marks, which is later queried.
The column name must meet the following criteria.
1. The column name cannot be a reserved word.
2. The first letter of the column name must be a letter.
3. The column name must be less than or equal to 30 characters.
4. The column name must be made up of alphanumeric characters.
5. The column name can contain any of the following 3 characters: $, _, #. If the column name includes any other characters, it must be enclosed with double quotation marks.
Follow the proper syntax by enclosing in double quotation marks.
WITH cte_month AS
(SELECT row_number() OVER(PARTITION BY to_char(r."balance_date", 'Month'), to_char(r."balance_date", 'Year') ORDER BY "balance_date" DESC) rownum,
arrears_in_days AS "arrears_in_days",
balance_date AS "balance_date",
acct_id AS "acct_id"
FROM report r
where r."acct_id" = '11002140008598'
and (to_char(r."balance_date", 'D')) not in (6, 7))
select xmlserialize(content "arrears_in_days" as clob)
from ( select * from cte_month )
pivot xml
(
max("arrears_in_days")
for ("balance_date")
in (select distinct "balance_date" from cte_month)
);
Reference: https://www.tekstream.com/resource-center/ora-00904-invalid-identifier/
