Oracle CASE短路不能分组工作

Oracle Database uses short-circuit evaluation. That is,for a simple
CASE expression,the database evaluates each comparison_expr value
only before comparing it to expr,rather than evaluating all
comparison_expr values before comparing any of them with expr.
Consequently,Oracle never evaluates a comparison_expr if a previous
comparison_expr is equal to expr. For a searched CASE expression,the
database evaluates each condition to determine whether it is true,and
never evaluates a condition if the previous condition was true.

但是以下SQL返回的除数等于零：

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

任何避免除数的解决方案都等于零误差？

编辑

此查询工作正常：

WITH data AS (SELECT 1 AS cond,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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!