CODEWITHSUNDEEP
sqloracle11g
user466534
user466534

Reputation:

optimize sql code

guys i have to select every employees,who has salary more then avarage salary in his department,there one code which does this problem,it is here

select * from
(select  e.first_name || ' ' || e.last_name ENAME,
d.department_name,e.salary, trunc(e.salary-avg(salary) over (partition by
  e.department_id)) sal_dif
  from employees e,departments d
  where  e.department_id=d.department_id)
  where sal_dif>0

when i run it,it works fine and also cost of this code is 6 in my computer,i have tried to do it on another way,like this

select   first_name || ' '|| last_name,salary
  from
  (select first_name || ' ' || last_name,salary ,avg(salary) over (partition by  department_id) avg_salary
  from employees )
  where salary>avg_salary;

i am surprised that,inspite of existence of last_name and first_name in database,,error 

shows me ORA-00904: "LAST_NAME": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 8 Column: 29

why?can i rewrite first code in other way?thanks a lot

Upvotes: 1

Views: 107

Answers (3)

user359040
user359040

Reputation:

As an alternative to aliasing the combined column inside the subquery, don't combine the columns in the subquery - instead, replace the concatenation with a ,, like so:

select   first_name || ' '|| last_name,salary
from
  (select first_name, last_name, salary,
          avg(salary) over (partition by  department_id) avg_salary
   from employees)
where salary>avg_salary;

Upvotes: 1

aF.
aF.

Reputation: 66697

Use alias to identify the column:

select   full_name,salary
  from
  (select first_name || ' ' || last_name as full_name,salary ,avg(salary) over (partition by  department_id) avg_salary
  from employees )
  where salary>avg_salary;

Upvotes: 1

YXD
YXD

Reputation: 32511

select name,salary
from
(select first_name || ' ' || last_name as name,salary ,avg(salary) over (partition by     department_id) avg_salary
  from employees )
  where salary>avg_salary;

Upvotes: 1

Related Questions