Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-03-2016
12:21 PM

02-03-2016
12:21 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-03-2016
01:27 PM

02-03-2016
01:27 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-03-2016
01:37 PM

02-03-2016
01:37 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-05-2016
07:35 AM

02-05-2016
07:35 AM

Re: DECODE Does Not Trap Divide by Zero Error

Excellent solution Dieter, thanks much for the help!