Oracle Invalid Identifier Error

Question

I know this has been asked before but I've looked at other questions and my query still won't work. I have a table with MLB batting stats from last 100 years or so, I am trying to find the playerid, homeruns and percentage of that year's (2012) total homerun's hit that player's hrs make up.

query:

select playerid, hr, hr/sum(hr) over (partition by playerid, yearid)*100 p
from mlbbattingstats 
where yearid=2012 and p != 0
order by hr;

error:

Error at line 3:
ORA-00904: "P": invalid identifier

I have tried multiple different aliases and gotten the same error. Any help in what I am doing wrong would be appreciated and sorry if this has been answered previously.

possible duplicate: http://stackoverflow.com/questions/3852831/how-to-use-alias-in-where-clause — ron tornambe, May 17 '15 at 19:33

score 2 · Accepted Answer · 2015-05-17T19:48:30.250

2

You can't reference a column alias on the same query level (except for order by). You need to wrap the statement into a derived table:

select *
from (
  select playerid, hr, hr/sum(hr) over (partition by playerid, yearid)*100 p
  from mlbbattingstats 
  where yearid = 2012 
) 
where p <> 0
order by hr;

edited May 17 '15 at 19:48

answered May 17 '15 at 19:34

thanks, that makes sense but now I get the error on ERROR at line 6: ORA-00904: "YEARID": invalid identifier – user3587186 May 17 '15 at 19:48
@user3587186: ah right. Copy and paste error, sorry. You can move the `yearid = 2012` into the derived table (what I did) or add the `yearid` to the select list and keep both where conditions together on the outer query (performance wise it won't matter for this simple query) – May 17 '15 at 19:50

score 1 · Answer 2 · answered May 17 '15 at 21:00

If p <> 0, then hr <> 0. So, your query would seem to be equivalent to:

select playerid, hr,
       hr/sum(hr) over (partition by playerid, yearid)*100 as p
from mlbbattingstats 
where yearid = 2012 and hr <> 0
order by hr;

Your original problem is that you cannot use a column alias defined in a select in the where clause as the same level.

Oracle Invalid Identifier Error

2 Answers2