divide by zero error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

divide by zero error

Author

Topic

Matlotlo
Starting Member

2 Posts

Posted - 2015-03-19 : 09:01:45

Hi,
Please help...

I'm getting this error:
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

this is where i'm getting the error:
CASE WHEN Claims.ClaimTotal > 0 then (ISNULL(Claims.ClaimTotal,0) / ISNULL(TotalPrem.TotalPrem,dbo.TrnPolicy.TotalDue)) else 0 END AS LossRatio

Kristen
Test

22859 Posts

Posted - 2015-03-19 : 09:06:56

easiest workaround is:


CASE WHEN Claims.ClaimTotal > 0 then (ISNULL(Claims.ClaimTotal,0) 
    / NullIf(ISNULL(TotalPrem.TotalPrem,dbo.TrnPolicy.TotalDue)) , 0)

else 0 END AS LossRatio

but probably better to take care (in your code) of the situation where your divisor is zero

Matlotlo
Starting Member

2 Posts

Posted - 2015-03-19 : 09:37:45

Thank you Kristen

I now get the Syntax error

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 59
Incorrect syntax near 'TotalPrem'.
Msg 102, Level 15, State 1, Line 72
Incorrect syntax near 'Claims'.

Matlotlo

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2015-03-19 : 10:07:59

CASE WHEN Claims.ClaimTotal > 0 then (ISNULL(Claims.ClaimTotal,0))
/ NullIf(ISNULL(TotalPrem.TotalPrem,dbo.TrnPolicy.TotalDue)) , 0)

Hema Sunder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources