Oracle - коррелированные проблемы подзапроса

Question

У меня есть этот запрос:

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;

Идея состоит в том, чтобы получить все acc_nums (не первичный ключ) из ac_tab, что есть owner из 1234567,

С acc_num в ac_tab мог измениться ownerСо временем я пытаюсь использовать внутренние коррелированные подзапросы, чтобы acc_num возвращается ТОЛЬКО если это последнее owner является 12345678, Естественно, это не работает (или я не буду публиковать здесь;))

Oracle дает мне ошибку: ORA-000904 ac_outer.acc_num is an invalid identifier,

я думал так ac_outer должны быть видны коррелированным подзапросам, но по какой-то причине это не так. Есть ли способ исправить запрос, или мне нужно прибегнуть к PL/SQL, чтобы решить эту проблему?

(Верификация Oracle - 10 г)

Answer 1

Я не уверен, почему Питер использует аналитическую функцию Min(владелец) вместо first_value(владелец). Я считаю, что последний дает вам то, что вам нужно, в то время как мин (владелец) дает вам "минимальный" владелец. Все остальное в запросе я согласен с:

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 2

Я не вижу, что вам нужно dbms_random.value() for, но следующий запрос с использованием аналитических функций должен дать ожидаемый результат:

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;

Подзапрос дает вам владельца и самого последнего владельца в acc_num, который затем можно сравнить во внешнем запросе.

Answer 3

Я думаю, что вы теряете область действия для псевдонима "AC_OUTER", вкладывая два глубоко в свой подзапрос. Я, очевидно, не знаю вашу схему, но полагаюсь на max(date) для любой операции сортировки и rownum. Почему бы вам не попробовать что-то вроде этого:

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 4

Вы должны использовать аналитическую функцию оракула, чтобы сделать это с помощью

Answer 5

Вы можете заменить ваши заказанные подзапросы одним NOT EXISTS, который проверяет, есть ли другие владельцы на более поздний срок.