Oracle TO_DATE выдает ORA-01843 при использовании в WHERE, но не в SELECT.
У меня проблема с (относительно) простым запросом, который я не понимаю, и я надеюсь, что кто-нибудь сможет мне в этом помочь.
Здесь у нас есть запрос:
SELECT TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM (SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ') a
WHERE
trunc(SYSDATE)
BETWEEN
TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
AND
TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD')
Любопытная вещь в этом запросе заключается в том, что он выдает ORA-01843 , но только с предложением WHERE. Если я удалю предложение WHERE, ошибка не возникнет.
Так это работает
SELECT TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM (SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ') a
Поскольку часть WHERE использует те же самые вещи, что и часть SELECT, я спрашиваю себя, как это возможно?
Некоторая справочная информация:
- Тип данных обоих столбцов (ABWENDDAT, ABWBEGDAT) — VARCHAR2(14).
- Я проверил содержимое столбцов, у нас есть только две записи, которые вызовут это исключение (записи: 99999999999999), но эти две записи фильтруются с помощью предложения WHERE ABWABTNR <> 'PASRZ'.
- Я также позаботился о том, чтобы все строки (для каждого оператора) были возвращены, поэтому после выполнения оператора я просматриваю все возвращенные строки (до конца).
Я также проверил stackoverflow и нашел несколько вопросов, которые идут в том же направлении, но я не нашел вопроса с ответом, который бы мне помог или объяснил поведение.
Я думаю, что причиной такого поведения может быть план выполнения (или приоритет выполнения). Таким образом, две строки, которые могут вызвать ошибку, фильтруются после WHERE, который выдает ORA-01843, но перед частью SELECT. может ли это быть правдой, и если да, есть ли у кого-нибудь идеи, как я могу изменить запрос, чтобы он работал?
Заранее спасибо!
3 ответа
Возможно, предикат
TRUNC(sysdate) BETWEEN TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
AND TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD')
помещается в ваш внутренний запрос, который должен отфильтровывать недаты.
(SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ')
что означает, что этот запрос фактически становится
SELECT *
FROM babw
WHERE ABWABTNR <> 'PASRZ'
AND TRUNC(sysdate) BETWEEN TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
AND TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD')
причина вашей ошибки
«Лучшие» вещи по порядку:
- Сохраняйте даты только в столбце с типом данных даты.
- Найдите и исправьте неправильные даты.
При отсутствии любого из этих событий вы можете использоватьno_push_pred
подсказка, чтобы избежать помещения предиката во внутренний запрос
SELECT /*+ no_push_pred(a) */
TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM (SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ') a
WHERE
trunc(SYSDATE)
BETWEEN
TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
AND
TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD')
SQL Engine предпочитает переписать ваш запрос без вложенных подзапросов, чтобы ваш первый запрос эффективно:
SELECT TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD'),
TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM babw
WHERE ABWABTNR <> 'PASRZ'
AND trunc(SYSDATE) BETWEEN TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
AND TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD')
иBETWEEN
предложение оценивается до того, какABWABTNR
сравнение.
Вы можете попробовать использовать подсказки, чтобы решить проблему. Или:
-
/*+ no_push_pred(a) */
во внешнем запросе; или -
/*+ no_merge */
во внутреннем запросе.
Или вы можете материализовать внутренний запрос, используяROWNUM
:
SELECT TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM (
SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ' AND ROWNUM > 0
) a
WHERE trunc(SYSDATE) BETWEEN TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
AND TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD');
Или вы можете использоватьCASE
выражение:
SELECT TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD'),
TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM babw
WHERE CASE
WHEN ABWABTNR <> 'PASRZ'
AND trunc(SYSDATE) BETWEEN TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
AND TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD')
THEN 1
END = 1;
Это возможно, потому что не только каждая строка в этой таблице имеет как .ABWENDDAT, так и .ABWENDDAT, чтобы их можно было преобразовать в дату с этим форматом. Если у вас версия Oracle >12, вы можете использовать TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8) по умолчанию, ноль при ошибке преобразования, «ГГГГММДД»). Если у вас есть одна строка, в которой это преобразование будет невозможно, и у вас нет значения по умолчанию, равного нулю при ошибке преобразования, то эта единственная строка вызовет исключение. Или, если версия Oracle <12, вам следует сначала отфильтровать строки, в которых данные в этих столбцах невозможно преобразовать к дате, используя выражение общей таблицы.