 Database Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print All Forums Database 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   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 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 05 Feb 2016 Excellent solution Dieter, thanks much for the help! You must sign in to leave a comment.