Using alias in SELECT statement gives invalid identifier error 1

[sjh]

Hi,
I have the following SQL statement that uses a column alias "SUMAREAS". When I execute it, I get [ORA-00904 : Invalid Identifier] error message. Is there a way to get around this problem?

Thank you!!
Susie

SELECT
SUM( A + B ) AS SUMAREAS,
Round(SUMAREAS/2,2) as AVGAREA

FROM myTable

 

[SantaMufasa]

Susie,

Yes, you can modify your code to read as follows:

select sumareas, Round(SUMAREAS/2,2) as AVGAREA
from (SELECT SUM( A + B ) AS SUMAREAS from myTable);

Let us know of your success.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 20:42 (04Feb05) UTC (aka "GMT" and "Zulu"),
@ 13:42 (04Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[sjh]

Hi Mufasa,

I actually need to select other values from the myTable. I tried your suggestion, but it didn't work. Here's the SQL statement that I tried.

SELECT

me.ykey as YearValue,
SUM( rating ) AS AreaRating,
SUMAREAS,
round( SUMAREAS/2 ,2) as AVGAREA

FROM (SELECT SUM( A + B ) AS SUMAREAS from myTable me)
WHERE ((me.district IN ('01','02','03'))) AND me.typeKey = 'RB'

GROUP BY me.ykey


I just resorted to using the expression "SUM( A + B )" again instead of using the alias.

Thank for your help and suggestion though! I appreciate it very much!

Susie

 

[SantaMufasa]

Susie,

If you are still interested in getting the original code to work, try this:

select YearValue
      ,AreaRating
      ,sumareas, Round(SUMAREAS/2,2) as AVGAREA
from (SELECT SUM( A + B ) AS SUMAREAS
      ,ykey YearValue
      ,sum(rating) AreaRating
      from myTable
      WHERE district in ('01','02','03')
        AND typeKey = 'RB'
      Group by yKey);

Let us know if it worked just for closure on the topic.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:23 (04Feb05) UTC (aka "GMT" and "Zulu"),
@ 15:23 (04Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[sjh]

Thank you Mufasa!

It worked!

Gratefully,
Susie