DECODE Does Not Trap Divide by Zero Error

Database
Enthusiast

DECODE Does Not Trap Divide by Zero Error

I've been beating my head against Teradata's wall this week and cannot figure out why this isn't working. We use this code in Oracle and recently ported data over to Teradata. The CASE and DECODES are as is from Oracle, however, unlike Oracle I get 2618 invalid calculation, division by zero.

Why does this work in Oracle and not teradata?

Note there are 39 formula keys (1...39) and 1, 2 & 3 are the simplist. It fails on 2 and 3. I figure if I can understand how Teradata is evaluating this and not capturing the division by zero on 2 and 3 then I can probably figure out the remaining formulas.

Thanks

CASE

          WHEN rl.formula_key = 1

             THEN SUM (DECODE (variable_code,'V1', CurrentActualAmount,0))

          WHEN rl.formula_key = 2          -- V1 / V2

             THEN DECODE ((SUM(DECODE(variable_code, 'V2', CurrentActualAmount, 0))), 0, 0, (SUM (DECODE (variable_code, 'V1', CurrentActualAmount, 0)) / SUM (DECODE (variable_code, 'V2', CurrentActualAmount, 0))))

          WHEN rl.formula_key = 3          -- (V1/V2)*100

             THEN DECODE((SUM(DECODE(variable_code, 'V2', CurrentActualAmount, 0))), 0, 0, ((100 * SUM(DECODE(variable_code, 'V1', CurrentActualAmount, 0)) / SUM(DECODE(variable_code, 'V2', CurrentActualAmount, 0))) ))

          ELSE 0

       END AS actual_amount

3 REPLIES
Enthusiast

Re: DECODE Does Not Trap Divide by Zero Error

Steve,

DECODE is very complex. You can convert DECODE into CASE statements.

For capturing division by zero, you can use NULLIF or NULLIFZERO functions.

Thanks,

Dinesh

Junior Contributor

Re: DECODE Does Not Trap Divide by Zero Error

DECODE is not built-in, but a UDF. It's probably not following the same rules as a CASE, i.e. seems to calculate everything before deciding which value to return.

The only workaround might be adding NULLIF for division:

    / NULLIF(SUM (DECODE (variable_code, 'V2', CurrentActualAmount, 0)),0)

But then you don't need the outer DECODE anymore, after switching to CASE:

CASE
WHEN rl.formula_key = 1
THEN SUM(CASE WHEN variable_code = 'V1' THEN CurrentActualAmount ELSE 0 END)
WHEN rl.formula_key = 2 -- V1 / V2
THEN COALESCE(SUM(CASE WHEN variable_code = 'V1' THEN CurrentActualAmount ELSE 0 END)
/ NULLIF(SUM(CASE WHEN variable_code = 'V2' THEN CurrentActualAmount ELSE 0 END),0)
,0)
WHEN rl.formula_key = 3 -- (V1/V2)*100
THEN COALESCE(100 * SUM(CASE WHEN variable_code = 'V1' THEN CurrentActualAmount ELSE 0 END)
/ NULLIF(SUM(CASE WHEN variable_code = 'V2' THEN CurrentActualAmount ELSE 0 END),0)
,0)
ELSE 0
END AS actual_amount
Enthusiast

Re: DECODE Does Not Trap Divide by Zero Error

Excellent solution Dieter, thanks much for the help!