Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle CASE short-circuit not working in group by

I've found in the documentation of case statment that it uses short-circuit:

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.

But the following SQL is returning 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

Any solution to avoid divisor is equal to zero error?

EDIT

This query works fine:

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
like image 488
Miquel Avatar asked Nov 19 '25 07:11

Miquel


1 Answers

Use

CASE WHEN cond = 2 
     THEN SUM(case when div = 0 then 0 else num / div end)
     ELSE -1
END
like image 138
juergen d Avatar answered Nov 21 '25 09:11

juergen d



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!