Oracle TO_DATE在WHERE而不是SELECT中使用时抛出ORA-01843

q9rjltbz 于 2023-05-28 发布在 Oracle

关注(0)|答案(3)|浏览(163)

我有一个问题，一个（相对）简单的查询，我不明白，我希望有人能帮助我在这方面。
这里我们有一个查询：

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）的数据类型都是VARCHAR 2（14）
我检查了列的内容，我们只有两个条目会触发此异常（条目是：99999999999999），但这两个条目是用WHERE ABWABTNR <> 'PASRZ'子句过滤的。
我还确保了所有的行（每个语句）都被返回，所以在我执行一个语句之后，我会遍历所有返回的行（直到最后）。

我还检查了stackoverflow，发现了一些相同方向的问题，但我没有发现一个问题的答案对我有用或解释了行为。
我认为这种行为的原因可能是执行计划（或执行的准确性）。因此，可能触发错误的两行被过滤在抛出ORA-01843的WHERE之后，但在SELECT部分之前。这是真的吗？如果是真的，有人知道如何更改查询，使其正常工作吗？
感谢您的评分

oracle

来源：https://stackoverflow.com/questions/76322667/oracle-to-date-throws-ora-01843-when-used-in-where-but-not-in-select

3条答案

按热度按时间

5gfr0r5j1#

SQL引擎选择重写查询，而不使用嵌套的子查询，因此您的第一个查询实际上是：

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

并且在ABWABTNR比较之前评估BETWEEN子句。
您可以尝试使用提示来解决问题。或者：

外部查询中的/*+ 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;

赞(0）回复(0）举报 2023-05-28

qojgxg4l2#

有可能 predicate

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提示来避免将 predicate 推入内部查询

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

赞(0）回复(0）举报 2023-05-28

l7wslrjt3#

这是可能的，因为并非该表中的每一行都具有.ABWENDDAT和.ABWENDDAT，例如可以转换为该格式的日期。如果你有Oracle版本>12，那么你可以使用TO_DATE（SUBSTR（a.ABWBEGDAT，1，8）default null on conversion error，'YYYYMMDD'）。如果有一行无法进行这种转换，并且转换错误时没有默认的null，那么这一行将导致异常。或者，如果Oracle版本<12，那么您应该首先过滤掉那些列中的数据不能使用公共表表达式转换为日期的行。

赞(0）回复(0）举报 2023-05-28

我来回答

Oracle TO_DATE在WHERE而不是SELECT中使用时抛出ORA-01843

3条答案

相关问题

热门标签

最新问答