optimize sql code

Question

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

aF. · Accepted Answer · 2012-02-07 11:11:40Z

1

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;

answered Feb 7, 2012 at 11:11

aF.

67k45 gold badges141 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

YXD · Accepted Answer · 2012-02-07 11:09:39Z

1

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;

answered Feb 7, 2012 at 11:09

YXD

32.6k15 gold badges79 silver badges117 bronze badges

Comments

user359040 · Accepted Answer · 2012-02-07 11:36:06Z

1

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;

answered Feb 7, 2012 at 11:36

user359040

Collectives™ on Stack Overflow

optimize sql code

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related