• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1417
  • Last Modified:

Divide by zero error encountered

Hi All,
How can avoid the following message and continue with calculations with "valid data" fields

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
The statement has been terminated.

[dbo].[BILLING.E164].CALL_DURATION * CAST([dbo].[BILLING.R001.CLIENT.PLAN.L0].PRICE_SECCOND_0 AS DECIMAL(27, 5)) /
[dbo].[BILLING.E164].CALL_DURATION * CAST([dbo].[BILLING.R001.CLIENT.PLAN.L0].PRICE_SECCOND_2 AS DECIMAL(27, 5)) * 100 AS SAVING_PERCENTAGE

Thanks in Advance!
0
batman_k
Asked:
batman_k
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can decide on what value you want when the divisor is 0:

also, try to use table aliases ...
CASE WHEN x.CALL_DURATION * CAST(l.PRICE_SECCOND_2 AS DECIMAL(27, 5)) = 0 THEN 0
ELSE 
x.CALL_DURATION * CAST(l.PRICE_SECCOND_0 AS DECIMAL(27, 5)) /
CASE WHEN x.CALL_DURATION * CAST(l.PRICE_SECCOND_2 AS DECIMAL(27, 5))= 0 THEN 1 ELSE x.CALL_DURATION * CAST(l.PRICE_SECCOND_2 AS DECIMAL(27, 5)) * 100 END AS SAVING_PERCENTAGE
...

FROM [dbo].[BILLING.E164] x
JOIN [dbo].[BILLING.R001.CLIENT.PLAN.L0] l

Open in new window

0
 
ThakurVinayCommented:
there is a CASE =0 for the value which you are dividing and if it is 0 dont do that expression.

HTH
Vinay
0
 
cyberkiwiCommented:
I normally prefer the nullif, isnull route

ISNULL([dbo].[BILLING.E164].CALL_DURATION * CAST([dbo].[BILLING.R001.CLIENT.PLAN.L0].PRICE_SECCOND_0 AS DECIMAL(27, 5)) /
NULLIF([dbo].[BILLING.E164].CALL_DURATION * CAST([dbo].[BILLING.R001.CLIENT.PLAN.L0].PRICE_SECCOND_2 AS DECIMAL(27, 5)) * 100,0),0) AS SAVING_PERCENTAGE
0
 
PrakashRaoBSCommented:
Try this..

if ([dbo].[BILLING.E164].CALL_DURATION > 0 and [dbo].[BILLING.R001.CLIENT.PLAN.L0].PRICE_SECCOND_2 > 0)
begin
dbo].[BILLING.E164].CALL_DURATION * CAST([dbo].[BILLING.R001.CLIENT.PLAN.L0].PRICE_SECCOND_0 AS DECIMAL(27, 5)) /
[dbo].[BILLING.E164].CALL_DURATION * CAST([dbo].[BILLING.R001.CLIENT.PLAN.L0].PRICE_SECCOND_2 AS DECIMAL(27, 5)) * 100 AS SAVING_PERCENTAGE
end
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now