Some companies in finance and insurance use Oracle PL/SQL to implement business logic in their Oracle database. This is not everyone's cup of tea, because this language has some unexpected pitfalls. Today I stumbled upon a new pitfall. What might be wrong with the following piece of code?

DELCARE l_n_sum NUMBER;
DECLARE l_n_result NUMBER;
DECLARE l_v_some_value NUMBER;

SELECT CASE sum(t1.count)
    WHEN null then l_n_sum
    WHEN 0 then l_n_sum
    ELSE sum(t1.count)
    END
  INTO l_n_result
  FROM yourschema.some_table t1
 where t1.value = l_v_some_value;

In this snipped we sum up count for all entries in yourschema.some_table where value is equal to l_v_some_value and assign the result to l_n_result. However in the case where the sum is null or zero we want to assign the value of l_n_sum. By the way: this is some extended version of the built in NVL() function.

This snipped does not what we want it to do. It will always assign the value in the ELSE branch as PL/SQL does not support aggregate functions after CASE. You can only use values at this position. If you want the semantics described above you have to write out the comparison in the WHEN branch.

DELCARE l_n_sum NUMBER;
DECLARE l_n_result NUMBER;
DECLARE l_v_some_value NUMBER;

SELECT CASE
WHEN sum(t1.count) is null then l_n_sum
WHEN sum(t1.count) = 0 then l_n_sum
ELSE sum(t1.count)
END
INTO l_n_result
FROM yourschema.some_table t1
where t1.value = l_v_some_value;