CODEWITHSUNDEEP
sqloracle-database
Cole
Cole

Reputation: 11255

Oracle CASE WHEN Short-Circuit Return Expressions

I have a table in which based on the type, I want to aggregate values using either the arithmetic mean - AVG(value) - or the geometric mean - EXP(AVG(LN(value))). My attempt is with a CASE WHEN statement but the issue is that some values can be 0 which means the LN(0) part will fail.

WITH tbl AS
  (
     SELECT 0 as val, 'avg' as calc_type FROM DUAL
     UNION ALL
     SELECT 1 as val, 'geom_mean' FROM DUAL
  )
SELECT CASE WHEN max(calc_type) = 'avg' THEN AVG(val)
       ELSE EXP(AVG(LN(val))) END as res
FROM tbl
GROUP BY calc_type

ORA-01428: argument '0' is out of range

Based on Oracle's documentation, it appears this is expected as the return_exprs are evaluated for type stability.

For both simple and searched CASE expressions, all of the return_exprs must either have the same datatype (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric datatype.

While I could do a filter for each calculation type and then UNION ALL the results, it would be ideal to do it in one query as I have to join a lot of tables to get to this step.

Upvotes: 0

Views: 487

Answers (2)

The AG
The AG

Reputation: 690

Since for LN to work, value must be greater than 0. How is this:

 WITH tbl AS
  (
     SELECT 0 as val, 'avg' as calc_type FROM DUAL
     UNION ALL
     SELECT 1 as val, 'geom_mean' FROM DUAL
  )
SELECT CASE WHEN max(calc_type) = 'avg' THEN AVG(val)
        WHEN max(calc_type) = 'geom_mean' AND val >0 THEN EXP(AVG(LN(val))) ELSE NULL END as res
FROM tbl
GROUP BY calc_type

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Oracle does process the CASE expressions in order. Unfortunately, because the aggregation, it also needs to evaluate all the aggregation expressions before it gets around to the CASE. And that is where the error is occurring. Note: I'm not sure where this is documented for Oracle, but I've seen the problem in other databases.

The solution is to use another CASE expression to avoid errors:

WITH tbl AS (
      SELECT 0 as val, 'avg' as calc_type FROM DUAL
      UNION ALL
      SELECT 1 as val, 'geom_mean' FROM DUAL
     )
SELECT CASE WHEN max(calc_type) = 'avg' THEN AVG(val)
       ELSE EXP(AVG(LN(CASE WHEN val > 0 THEN val END))) END as res
FROM tbl
GROUP BY calc_type

Upvotes: 2

Related Questions