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)