Oracle CASE短路不按组工作 [英] Oracle CASE short-circuit not working in group by

问题描述

我已经在案卷中的文档中找到了使用短路:

Oracle数据库使用短路评估.也就是说，对于一个简单的 CASE表达式，数据库评估每个compare_expr值 仅在将其与expr进行比较之前，而不是评估所有结果之前 将其中任何一个与expr进行比较之前，必须先使用compare_expr值. 因此，如果以前 comparison_expr等于expr.对于搜索的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返回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错误?

Any solution to avoid divisor is equal to zero error?

编辑

此查询工作正常:

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

这篇关于Oracle CASE短路不按组工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！