Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Help Simplifying SQL query

Help Simplifying SQL query

From: Montse <nospam_at_mysticdragon.com>
Date: 19 Mar 2003 09:23:05 -0800
Message-ID: <a4beba43.0303190923.61192936@posting.google.com>


Hi there,

I'm new to SQL, only been working with a few months. I've created this query that does exactly what I need. However, it is very apparent that I can simplify it as I am reusing the same select statement code 3 times.

It seems I should be able place this select statement in the from statement once and then use an alias or something to access it earlier in the statement.

Any ideas on how to do this without using views?

Thanks in advance!

select emp_lname, emp_fname, dept_name, salary,

	salary -

(select avg(salary) from sales2.employee e2
where dept_id=e.dept_id ) as Difference,
(select avg(salary) from sales2.employee e2
where dept_id=e.dept_id ) as AVERAGE

from sales2.employee e, sales2.department d where d.dept_id = e.dept_id
and
	salary >

(select avg(salary) from sales2.employee e2
where dept_id=e.dept_id group by dept_id)

order by dept_name
/ Received on Wed Mar 19 2003 - 11:23:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US