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

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