CODEWITHSUNDEEP
sqloracle-database
user3587186
user3587186

Reputation: 49

Oracle Invalid Identifier Error

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.

Upvotes: 0

Views: 525

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 1

user330315
user330315

Reputation:

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;

Upvotes: 2

Related Questions