Oracle数据库使用短路评估。也就是说,对于一个简单的CASE表达式,数据库仅在将每个compare_expr值与expr进行比较之前才对其求值,而不是在将任何一个compare_expr值与expr进行比较之前才求值。因此,如果先前的compare_expr等于expr,则Oracle永远不会评估comparison_expr。对于搜索的CASE表达式,数据库将评估每个条件以确定其是否为真,并且如果先前的条件为真,则从不评估条件。
但是下面的SQL正在返回divisor is equal to zero
:
WITH data AS (SELECT 1 AS cond, 10 AS num, 0 AS div FROM DUAL)
SELECT
CASE WHEN cond = 2 THEN (CASE WHEN MAX(div) = 0 THEN 0 ELSE SUM(num / div) END)
ELSE -1
END AS result
FROM data
GROUP BY cond
有什么办法可以避免divisor is equal to zero
错误?
编辑
此查询工作正常:
WITH data AS (SELECT 1 AS cond, 10 AS num, 0 AS div FROM DUAL)
SELECT
CASE WHEN cond = 2 THEN (CASE WHEN MAX(div) = 0 THEN 0 ELSE 1 END)
ELSE -1
END AS result
FROM data
GROUP BY cond
用
CASE WHEN cond = 2
THEN SUM(case when div = 0 then 0 else num / div end)
ELSE -1
END
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句