Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

why I can not assign an alias to a query in the from clause?

jane zhang
Regular Advisor

why I can not assign an alias to a query in the from clause?

Hi all,
I have a simple query like this
select deptno, avg(temp.max_sal)
from ( select deptno, max(sal) as max_sal
from emp
group by deptno) as temp
where temp.max_sal >=5000

When I execute this query, I got error message: ORA-00933: SQL command not properly ended.
Can anybody tell me why?
Jane
3 REPLIES
Dave Burton_1
Occasional Visitor

Re: why I can not assign an alias to a query in the from clause?

Jane,

You need to "group by deptno) temp", rather than "group by deptno) as temp"
Tom Geudens
Honored Contributor

Re: why I can not assign an alias to a query in the from clause?

Hi,
I'm not sure, but is this what you're trying to do ?
SQL> select deptno,"salary" from
(select deptno, max(sal) "salary" from emp group by deptno)
where "salary" >= 5000;

Hope it helps,
Tom Geudens
A life ? Cool ! Where can I download one of those from ?
jane zhang
Regular Advisor

Re: why I can not assign an alias to a query in the from clause?

Thank you,
My query is now working!
Jane