SQLTeam.com | Weblogs | Forums

Divide by zero error


#1

I have the following statement :

          Update snapshot2 
     set verwagteoesteenoorverledejaar = skattingvirkulttotaal + NULLIF(ISNULL(tendensverledejaar /100, 0), 0) * skattingvirkulttotaal 

I get the following error : Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

I want the calculation to ignore the tendensverledejaar/100 if tendensverledejaar = 0

Regards


#2

Maybe:

update snapshot2
   set verwagteoesteenoorverledejaar = skattingvirkulttotaal + ISNULL(tendensverledejaar /100, 0) * skattingvirkulttotaal
 where tendensverledejaar<>0
;

#3

I can't see that is ever going to be a "Divide by zero error encountered."

SELECT 1 / 100
SELECT 1.0 / 100
SELECT CONVERT(int, NULL) / 100

are all fine.

The error must be somewhere else. The workaround you [almost!] have of

IsNull(dividend / NullIf(divisor, 0), 0)

is the best way of handling a potential divide-by-zero issue gracefully


#4

Don't see any divide by 0 there. Look for a trigger(s) on the table.