(Oracle) Коррелированное использование подзапросов

Подзапрос query1 ниже работает нормально. Но когда я помещаю условие equal во вложенное предложение типа query2, оно показывает ошибку ORA-00904. Это неправильное использование коррелированного подзапроса или по другой причине?

--Query1: It shows expected result.

    SELECT
    O.ENAME
    O,SAL
    ,(SELECT COUNT(*)
        FROM SCOTT.EMP I
        WHERE I.SAL>O.SAL  --correlated to outer
     ) AS RESULT
    from SCOTT.EMP O;


--Query2:ORA-00904: "O"."SAL": invalid identifier shows. How to modify to use correlated subquery?

    SELECT
    O.ENAME
    O,SAL
    ,(
    WITH TEMP AS 
        (
        SELECT COUNT(*)
        FROM SCOTT.EMP I
        WHERE I.SAL>O.SAL   --I have put equi condistion here 
        )
    SELECT * FROM TEMP
    )  AS RESULT
    from SCOTT.EMP O;

oracle window-functions subquery correlated-subquery ora-00904
person Soon    schedule 25.07.2020    source источник
comment
Какую версию Oracle вы используете? До 12.1 (я думаю) существовало ограничение в один уровень в ссылках родитель/потомок; но не уверен, как это работает с CTE в более поздних версиях.   -  person Alex Poole    schedule 25.07.2020

Ответы (2)


arrow_upward
1
arrow_downward

Я считаю, что второй вариант — это неправильное использование коррелированных подзапросов не из-за сравнения, а из-за использования предложения with. Я хотел бы помнить, что вам следует избегать коррелированных подзапросов, насколько это возможно.

Предложение WITH или предложение факторинга подзапроса может обрабатываться как встроенное представление или разрешаться как временная таблица. Преимущество последнего заключается в том, что повторные ссылки на подзапрос могут быть более эффективными, поскольку данные легко извлекаются из временной таблицы, а не запрашиваются повторно при каждой ссылке.

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

SQL> create table emp ( ename varchar2(10) , sal number ) ;

Table created.

SQL> insert into emp values ( 'AAA' , 1000 ) ;

insert into emp values ( 'BBB' , 1000 ) ;

insert into emp values ( 'CCC' , 1000 ) ;

insert into emp values ( 'DDD' , 1000 ) ;
1 row created.

SQL> SQL>
1 row created.

SQL> SQL>
1 row created.

SQL> SQL>

1 row created.

SQL> select * from emp ;

ENAME             SAL
---------- ----------
AAA              1000
BBB              1000
CCC              1000
DDD              1000

Чтобы написать запрос со встроенным представлением, фильтр должен быть выполнен во внешнем запросе.

SELECT
    O.ENAME
    O,SAL
    ,(
    WITH TEMP AS 
        (
        SELECT * FROM EMP 
        )
    SELECT count(*) FROM TEMP t WHERE t.SAL>O.SAL
    )  AS RESULT
    from EMP O;
O                 SAL     RESULT
---------- ---------- ----------
AAA              1000          0
BBB              1000          0
CCC              1000          0
DDD              1000          0
person Roberto Hernandez    schedule 25.07.2020

arrow_upward
0
arrow_downward

Было объяснено, что второй запрос использует with неправильно.

Однако позвольте мне предложить, чтобы ваш запрос можно было сформулировать проще и эффективнее. Для каждого сотрудника вы хотите подсчитать, сколько сотрудников имеют большую зарплату. Оконные функции - это путь сюда:

select e.*, rank() over(order by salary desc) - 1 result
from scott.emp e
person GMB    schedule 25.07.2020