gpt4 book ai didi

sql - JOIN 打破 WHERE 子查询

转载 作者:行者123 更新时间:2023-12-04 19:37:22 33 4
gpt4 key购买 nike

我的数据库中有 3 个表。

  • PARENT_A 有一个“ID”主键列。
  • PARENT_B 有一个“ID”主键列。
  • CHILD 具有“PARENT_A_ID”和“PARENT_B_ID”外键列。它还有一个“START_DATE”列,这是一个 VARCHAR(遗憾的是,我无法更改它)。

现在,我有以下查询。

更新 - 我更新了子查询,使其更像我的实际代码。对 c2 有一个额外的限制,它保证 START_DATE 是一个有效的日期。

SELECT *
FROM PARENT_B pb
LEFT OUTER JOIN CHILD c1 ON c1.PARENT_B_ID = pb.ID
WHERE pb.ID IN
(
SELECT c2.PARENT_B_ID
FROM PARENT_A pa
LEFT OUTER JOIN CHILD c2 ON c2.PARENT_A_ID = pa.ID
WHERE TO_DATE(c2.START_DATE, 'mm/dd/yyyy') BETWEEN
ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -12) AND
(TRUNC(SYSDATE, 'MONTH') - 1)
AND c2.HAS_VALID_DATE = 1
);

此查询失败。我收到 ORA-01843: not a valid month 异常。但是,如果我删除第一个连接(查询的第 3 行),查询运行正常。

我不知道发生了什么。子查询本身运行良好,所有值都采用正确的日期格式。

有人知道发生了什么事吗?

最佳答案

问题几乎肯定是 CHILD 中至少有一些行的 start_date 字符串无法使用 mm/dd 转换为日期/yyyy 格式。由于 Oracle 可以按任何顺序评估谓词,我希望您在没有连接的情况下获得的计划是在执行 to_date 之前消除那些坏行,而您在连接中获得的计划是在出于其他原因删除行之前评估 to_date。当然,明天,优化器可能会选择一个不同的计划,并且查询可能会使用连接并在没有它的情况下失败。或者您可能会发现查询成功返回第一行,然后在您尝试获取这些行时抛出 ORA-01843 错误。

除非您可以修复数据,否则最好的选择通常是编写您自己的字符串到日期的转换例程,如果字符串无法转换为日期,则返回 NULL。有点像

CREATE OR REPLACE FUNCTION my_to_date( p_dt_str IN VARCHAR2, p_mask IN VARCHAR2 )
RETURN DATE
DETERMINISTIC
IS
l_dt DATE;
BEGIN
l_dt := to_date( p_dt_str, p_mask );
RETURN l_dt;
EXCEPTION
WHEN others THEN
RETURN NULL;
END;

然后你的查询会说

WHERE my_to_date( c2.start_date, 'mm/dd/yyyy' ) between ...

您还可以使用此函数查找 start_date 无效的行

SELECT *
FROM child
WHERE start_date is not null
AND my_to_date( start_date, 'mm/dd/yyyy' ) is null

Jonathan Gennick 有一篇很棒的文章 Subquery Madness更详细地讨论了这个问题。这也是一本有趣的书。

关于sql - JOIN 打破 WHERE 子查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10937364/

33 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com