All Forums Database
ssi112 5 posts Joined 08/11
03 Feb 2016
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

 

dins2k2 51 posts Joined 05/13
03 Feb 2016

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

dnoeth 4628 posts Joined 11/04
03 Feb 2016

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

 
 
 
 
 
 
 

Dieter

ssi112 5 posts Joined 08/11
05 Feb 2016

Excellent solution Dieter, thanks much for the help!

You must sign in to leave a comment.