I have a query that creates two aliased tables using with statements:
WITH Rev AS
(SELECT *
FROM FORECAST.REVENUE_SUMMARY
WHERE FORECAST.REVENUE_SUMMARY.FEE_CD_ACT_SUM_ACCTG_DA >= to_date('10/01/2013', 'mm/dd/yyyy')
AND FORECAST.REVENUE_SUMMARY.FEE_CD_ACT_SUM_ACCTG_DA < to_date('10/01/2014', 'mm/dd/yyyy')
),
Mon_pv as
(select to_char(FEE_CD_ACT_SUM_ACCTG_DA, 'MON') as "Mon",
Fee_CD,
Fee_NM,
SUM(CASH_DAILY_CL) as "Collections"
from Rev
group by to_char(FEE_CD_ACT_SUM_ACCTG_DA, 'MON'),
Fee_CD,
Fee_NM
)
select distinct Mon
from Mon_pv
;
in the Mon_pv table "Mon" is just a derived month column. so in the final query all I want to do is select a list of distinct month names, but it gives me an error saying that "Mon is an invalid identifier." Ultimately I want to be able to use it in a pivot table to create columns out of the month names. But any query using "Mon" as a column throws an error. But when I use Select * from Mon_pv "Mon" appears as a column. Why does it throw an error when I mention the column name specifically in the final Select statement?
"Mon"in the last part of the query too?"Mon"is a different identifier thanMon. See the manual for details: docs.oracle.com/cd/E11882_01/server.112/e41084/…