Oracle mechanics

27.08.2009

ora-904 ora-907 при использовании коррелированных подзапросов

Filed under: Oracle — Игорь Усольцев @ 18:41
Tags: ,

При использовании коррелированных подзапросов, т.е. подзапросов, результаты которых используются для каждой строки основного запроса — «a correlated subquery is evaluated once for each row», могут встречаются ошибки, неточно объясняющие, что делается неправильно

ora-904 «%s: invalid identifier»

SQL> select (select count(*) from
(select * from scott.emp where ename = dual.dummy)) from dual;
select (select count(*) from
(select * from scott.emp where ename = dual.dummy)) from dual
                                                        *
ERROR at line 1:
ORA-00904: "DUAL"."DUMMY": invalid identifier

ora-907 «missing right parenthesis»

SQL> select
(select empno from scott.emp where ename = dual.dummy order by 1)
from dual;
select
(select empno from scott.emp where ename = dual.dummy order by 1)
from dual
 *
ERROR at line 1:
ORA-00907: missing right parenthesis


Первая ошибка ora-904 в случае correlated subquery при отсутствии синтаксических ошибок сообщает об использовании более, чем одного уровня глубины вложенности (N-th level sub-query):

SQL> select (select count(*) from
(select * from scott.emp where ename = dual.dummy)) from dual;

select (select count(*) from
(select * from scott.emp where ename = dual.dummy)) from dual
                                       *
ERROR at line 1:
ORA-00904: "DUAL"."DUMMY": invalid identifier

что не поддерживается Oracle (до версии 11.1.0.7 включительно) и стандартом SQL: ANSI SQL has table references (correlation names) scoped to just one level deep. Это пишет вице-президент Oracle T.Kyte, отвечая на соответствующий вопрос «Is there some sort of nesting limit for correlated subqueries?», т.е. информация официальная, из первых рук, пример оттуда же. В документации Oracle, начиная с 10.1, пишется иначе:

Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery

— но, это, видимо, долгосрочные планы Oracle.

ora-907

SQL> select
(select empno from scott.emp where ename = dual.dummy order by 1)
from dual;

select
(select empno from scott.emp where ename = dual.dummy order by 1)
from dual
 *
ERROR at line 1:
ORA-00907: missing right parenthesis

тут проблема старая и известная: запрет на использование ORDER BY в подзапросах, описанная в документации Oracle 7 (в документации следующих версий уже не упоминается — видимо, тоже есть планы по исправлению):

The ORDER BY clause cannot appear in subqueries within other statements.

По вышеупомянутой ссылке на сайте asktom.oracle.com можно найти пример замены конструкции с ORDER BY типа:

(SELECT column_1 FROM
  (SELECT column_1 FROM table_1 WHERE ... ORDER BY ...) Q_1
 WHERE rownum = 1)

на аналитическую функцию типа :

(SELECT max(column_1) keep (dense_rank first ORDER BY ...)
 FROM table_1 WHERE ...)

для одновременного понижения уровня вложенности подзапроса (correlated query level deep) до 1 и устранения проблемы с ORDER BY в подзапросе — конструкция dense_rank first ORDER BY допускается Oracle.

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: