Oracle - SImple Question Big Ramifications

Question

I am new to Oracle and have run across following problem using quoted column aliases in a subquery.

The following query works:

select r.exp1 + r.exp2 result from
  (select 5+1 exp1, 6+7 exp2 from dual) r

However, this query generates an "r"."exp1" invalid identifier error:

select r.exp1 + r.exp2 result from
  (select 5+1 "exp1", 6+7 exp2 from dual) r

Is there a way to use quoted identifiers in a subquery, what I really want to do is something like:

select r.[First Sum] + r.exp2 result from
  (select 5+1 "First Sum", 6+7 exp2 from dual) r

Thanks Mike Thomas

Answer 1

The double quotes make the alias case-sensitive, which is why your exp1 example didn't work like you expected. Without double quotes, it evaluates to "EXP1", where in the subquery you had "exp1". You should just be able to continue to use double quotes in your outer query to achieve what you want:

select r."First Sum" + r.exp2 result
from   (select 5+1 "First Sum", 6+7 exp2 from dual) r