Oracle - correlated subquery problems

Question

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 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)

Answer 1

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;

Answer 2

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

Answer 3

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.

Answer 4

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;

Answer 5

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