Null error when dividing, but numerator and denominator runs fine when spilt up

Cosmo 0
Viewed 36

This one probably has a really obvious answer but I cannot figure out why its erroring out:

(sum(case when N.DOS_DURATION <= 3 then N.NURA_Claims else 0 end)/sum(case when N.DOS_DURATION <= 3 then N.SRD_Prem else 0 end)) as NURA_MBR_Q1

ERROR [2:1]:(SQLSTATE: 42911, SQLCODE: -419): DB2 SQL Error: SQLCODE=-419, SQLSTATE=42911, SQLERRMC=null, DRIVER=4.25.13
ERROR [2:1]:(SQLSTATE: 56098, SQLCODE: -727): DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-419;42911;, DRIVER=4.25.13

However, splitting up the numerator and denominator runs fine, and there’s no null or 0 values.

sum(case when N.DOS_DURATION <= 3 then N.NURA_Claims else 0 end) as Claims, 
sum(case when N.DOS_DURATION <= 3 then N.SRD_Prem else 0 end) as Premiums

I’m stumped! Any suggestions? numerator and denominator output

db2 sql
1 Answers

The error is related to the rules described at the Expressions link. Look at the Decimal arithmetic in SQL topic and the Table 4. Precision and scale of the result of a decimal division table.
You may resolve the problem either with the dec_arithmetic database configuration parameter or with data type casting.
Below is an example, when dec_arithmetic is set to its default value.

SELECT
  -- Wrong - negative precision:
  -- 31 - p + s - s' = 31 - 31 + 2 - 3 = -1
  -- You get the same error as in the question.
  --NURA_Claims / SRD_Prem

  -- Correct - we achieve the desired precision with 
  -- data type casting for one of the operands
  -- 31 - p + s - s' = 31 - 31 + 5 - 3 = 2
  DEC (NURA_Claims, 31, 5) / SRD_Prem
FROM (VALUES (DEC (2, 31, 2), DEC (1, 31, 3))) N (NURA_Claims, SRD_Prem)
Mark Barinstein0
Related