If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

But my tableOne 'outer query' reference is now two levels deep and Oracle complains.

But, I've been told that this is horrible Oracle SQL syntax and should be recoded anyways.

Problem is, in the Oracle documentation they talk about subqueries and correlated subqueries (Which I think I have since an inner query references an outer column) but none of them put the subqueries into the select portion (where it seems to just make sense).
I just don't see how this can be combined into a single statement because the seperate subqueries are doing a lot of aggregate functions on their own.

I could really use some help in this matter. How should I be writing this thing?

This is an example of using correlated subquery. Works fine, counts employees for each department. The drawback is that it need to execute 6 queries instead of just one. If I'd write such query, I'd get rid of subquery:

Correlated subquery might be real performnace killer - just this week I came accross a similar query which had two-level correlated subquery. Executing on tables about 10,000 records took 20+ minutes. After rewriting in manner I suggested above, less than one second.

AlesThe whole difference between a little boy and an adult man is the price of toys