1

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?

4
  • 1
    did you try changing the alias name? Commented Aug 24, 2015 at 19:21
  • 1
    What if you apply double quotes to "Mon" in the last part of the query too? Commented Aug 24, 2015 at 19:22
  • No hadn't tried either. But putting the column name in quotes in the last select statement worked. Never even occurred to me that I might have to do that. Commented Aug 24, 2015 at 19:28
  • "Mon" is a different identifier than Mon. See the manual for details: docs.oracle.com/cd/E11882_01/server.112/e41084/… Commented Aug 24, 2015 at 21:45

1 Answer 1

1

As already alluded to in the comments: "Mon" and Mon are not the same identifier. When you put an identifier in double-quotes, it becomes case-sensitive (and also allows a wider variety of characters). When you do not use double quotes, the name is automatically converted to uppercase. So Mon, MON, and mon are all read as MON, but "Mon" is not.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.