Oracle - correlated subquery problems
- by FrustratedWithFormsDesigner
I have this query:
select acc_num
from (select distinct ac_outer.acc_num, ac_outer.owner
from ac_tab ac_outer
where (ac_outer.owner = '1234567')
and ac_outer.owner = (select sq.owner
from (select a1.owner
from ac_tab a1
where a1.acc_num = ac_outer.acc_num
order by a1.a_date desc, a1.b_date desc, a1.c_date desc) sq
where rownum = 1)
order by dbms_random.value()) subq
order by acc_num;
The idea is to get all acc_nums (not a primary key) from ac_tab, that have an owner of 1234567.
Since an acc_num in ac_tab could have changed owners over time, I am trying to use the inner correlated subqueries to ensure that an acc_num is returned ONLY if it's most recent owner is 12345678. Naturally, it doesn't work (or I wouldn't be posting here ;) )
Oracle gives me an error: ORA-000904 ac_outer.acc_num is an invalid identifier.
I thought that ac_outer should be visible to the correlated subqueries, but for some reason it's not. Is there a way to fix the query, or do I have to resort to PL/SQL to solve this?
(Oracle verison is 10g)
