Oracle - correlated subquery problems

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 /*This is the line that gives me problems.*/
                                             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开发者_Python百科 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)

I'm not sure why Peter is using a Min(owner) analytic function instead of first_value(owner). I believe the latter gives you what you need while the min(owner) is giving you the "minimum" owner. Everything else in the query I agree with:

Select Distinct acc_num
From (
      Select
            acc_num,
            owner,
            first_value(owner) Over ( Partition By acc_num
                  Order By a_date Desc, b_date Desc, c_date Desc
                ) recent_owner
      From ac_tab
     )
Where owner = '1234567'
      And owner = recent_owner
Order By acc_num;

I don't see what you need the dbms_random.value() for, but the following query using analytic functions should give you the expected result:

Select Distinct acc_num
From (
  Select
    acc_num,
    owner,
    First_Value(owner) Over ( Partition By acc_num
                              Order By a_date Desc, b_date Desc, c_date Desc
                            ) recent_owner
  From ac_tab
)
Where owner = '1234567'
  And owner = recent_owner
Order By acc_num;

The sub-query gives you the owner and the most recent owner per acc_num, which can then be compared in the outer query.

I think you're losing scope on the "AC_OUTER" alias by nesting two deep with your subquery. I obviously don't know your schema, but would rely on max(date) over any sorting operation and rownum. Why don't you try something like this:

select ac_outer.acc_num, ac_outer.owner, max(a1.adate), max(a1.b_date), max(a1.c_date)
from   ac_tab "AC_OUTER"
where  ac_outer.owner = '1234567'
group by ac_outer.owner, ac_outer.acc_num;

you should use oracle analytical function to do this using partition by

You can replace your ordered subqueries with one NOT EXISTS, that checks if there are some other owners at a later date.