1с ошибка при выполнении операции над данными 22012 error division by zero

Обновлено: 03.07.2024

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

What is the best way to write SQL code so that I will never see this error message again?

I could do either of the following:

Add a where clause so that my divisor is never zero

I could add a case statement, so that there is a special treatment for zero.

Is the best way to use a NULLIF clause?

Is there better way, or how can this be enforced?

5,553 5 5 gold badges 30 30 silver badges 45 45 bronze badges 12.1k 4 4 gold badges 22 22 silver badges 24 24 bronze badges

19 Answers 19

In order to avoid a "Division by zero" error we have programmed it like this:

But here is a much nicer way of doing it:

Now the only problem is to remember the NullIf bit, if I use the "/" key.

12.1k 4 4 gold badges 22 22 silver badges 24 24 bronze badges A much nicer Way of doing it "Select dividend / nullif(divisor, 0) . " breaks if divisor is NULL. @ErikE, it is true. try running . select 1/nullif(null,0) . you get "The type of the first argument to NULLIF cannot be the NULL constant because the type of the first argument has to be known." Handle this by using "coalesce(FieldName,0)" . e.g. select 1/nullif(coalesce(null,0),0) @JohnJoseph I can’t tell if you’re agreeing with me or arguing with me.

In case you want to return zero, in case a zero devision would happen, you can use:

For every divisor that is zero, you will get a zero in the result set.

29.1k 21 21 gold badges 97 97 silver badges 124 124 bronze badges 2,856 1 1 gold badge 15 15 silver badges 12 12 bronze badges Some benchmarks reveal that COALESCE is slightly slower than ISNULL. However, COALESCE is in the standards so is more portable. @SQLGeorge While I agree with your argument, please note that there are cases that one cares more what is statistically correct than mathematically correct. In some cases when using statistics functions, 0 or even 1 is an acceptable result when divisor is zero. I think that @George and @James/ Wilson fundamentally misunderstand the question being asked. There are certainly business applications where returning a "0" is appropriate, even if it is not technically true from a mathematical point of view.

This seemed to be the best fix for my situation when trying to address dividing by zero, which does happen in my data.

Suppose you want to calculate the male–female ratios for various school clubs, but you discover that the following query fails and issues a divide-by-zero error when it tries to calculate ratio for the Lord of the Rings Club, which has no women:

You can use the function NULLIF to avoid division by zero. NULLIF compares two expressions and returns null if they are equal or the first expression otherwise.

Rewrite the query as:

Any number divided by NULL gives NULL , and no error is generated.

4,759 7 7 gold badges 44 44 silver badges 78 78 bronze badges Yes indeed, that is WAY BETTER than that other answer which has got so many upvotes. In your solution, you have at least a NULL, which indicates that you cannot provide a correct result. But if you convert the result from NULL to Zero, then you simply get wrong and misleading results.

You can also do this at the beginning of the query:

So if you have something like 100/0 it will return NULL. I've only done this for simple queries, so I don't know how it will affect longer/complex ones.

20.7k 5 5 gold badges 39 39 silver badges 59 59 bronze badges This "feels" so dirty but I love it! Needed it in a query that does aggregation and use CASE statement was not an option because then I had to add that column to the GROUP BY which totally changed the results. Making the initial query a subselect and then doing a GROUP BY on the outer query also changes the results because there is division involved. OK, so I still like this "solution" but like many of you probably felt, I felt there had to be a "cleaner" way. What if I forgot to re-enable the warnings? Or someone cloaned my code (that never happens, right?) and did not think about the warnings? Anyway, saw other answers about NULLIF(). I knew about NULLIF() but did not realize dividing by NULL returns NULL (I thought it would be an error). So. I went with the following: ISNULL( (SUM(foo) / NULLIF(SUM(bar),0) ), 0) AS Avg

You can at least stop the query from breaking with an error and return NULL if there is a division by zero:

However, I would NEVER convert this to Zero with coalesce like it is shown in that other answer which got many upvotes. This is completely wrong in a mathematical sense, and it is even dangerous as your application will likely return wrong and misleading results.

3,920 1 1 gold badge 20 20 silver badges 49 49 bronze badges

EDIT: I'm getting a lot of downvotes on this recently. so I thought I'd just add a note that this answer was written before the question underwent it's most recent edit, where returning null was highlighted as an option. which seems very acceptable. Some of my answer was addressed to concerns like that of Edwardo, in the comments, who seemed to be advocating returning a 0. This is the case I was railing against.

ANSWER: I think there's an underlying issue here, which is that division by 0 is not legal. It's an indication that something is fundementally wrong. If you're dividing by zero, you're trying to do something that doesn't make sense mathematically, so no numeric answer you can get will be valid. (Use of null in this case is reasonable, as it is not a value that will be used in later mathematical calculations).

So Edwardo asks in the comments "what if the user puts in a 0?", and he advocates that it should be okay to get a 0 in return. If the user puts zero in the amount, and you want 0 returned when they do that, then you should put in code at the business rules level to catch that value and return 0. not have some special case where division by 0 = 0.

That's a subtle difference, but it's important. because the next time someone calls your function and expects it to do the right thing, and it does something funky that isn't mathematically correct, but just handles the particular edge case it's got a good chance of biting someone later. You're not really dividing by 0. you're just returning an bad answer to a bad question.

Imagine I'm coding something, and I screw it up. I should be reading in a radiation measurement scaling value, but in a strange edge case I didn't anticipate, I read in 0. I then drop my value into your function. you return me a 0! Hurray, no radiation! Except it's really there and it's just that I was passing in a bad value. but I have no idea. I want division to throw the error because it's the flag that something is wrong.

Here is a simple piece of code where a division by zero occurs. I'm trying to catch it :

But the application crashes anyway (even though I put the option -fexceptions of MinGW).

Is it possible to catch such an exception (which I understand is not a C++ exception, but a FPU exception) ?

I'm aware that I could check for the divisor before dividing, but I made the assumption that, because a division by zero is rare (at least in my app), it would be more efficient to try dividing (and catching the error if it occurs) than testing each time the divisor before dividing.

I'm doing these tests on a WindowsXP computer, but would like to make it cross platform.

25.2k 27 27 gold badges 91 91 silver badges 116 116 bronze badges if (Divisor == 0) < std::cout << "Error."; exit; >. You then eliminate every cause of this happening. Then you can remove the conditional and ship your product with no divide-by-zeroes. Rule of Thumb: ALWAYS (and in case you missed the emphasis: ALWAYS!) check your inputs before doing calculations and you avoid these types of issues.

9 Answers 9

It's not an exception. It's an error which is determined at hardware level and is returned back to the operating system, which then notifies your program in some OS-specific way about it (like, by killing the process).

I believe that in such case what happens is not an exception but a signal. If it's the case: The operating system interrupts your program's main control flow and calls a signal handler, which - in turn - terminates the operation of your program.

It's the same type of error which appears when you dereference a null pointer (then your program crashes by SIGSEGV signal, segmentation fault).

You could try to use the functions from <csignal> header to try to provide a custom handler for the SIGFPE signal (it's for floating point exceptions, but it might be the case that it's also raised for integer division by zero - I'm really unsure here). You should however note that the signal handling is OS-dependent and MinGW somehow "emulates" the POSIX signals under Windows environment.

Here's the test on MinGW 4.5, Windows 7:

And right after executing the signal handler, the system kills the process and displays an error message.

Using this, you can close any resources or log an error after a division by zero or a null pointer dereference. but unlike exceptions that's NOT a way to control your program's flow even in exceptional cases. A valid program shouldn't do that. Catching those signals is only useful for debugging/diagnosing purposes.

(There are some useful signals which are very useful in general in low-level programming and don't cause your program to be killed right after the handler, but that's a deep topic).

Читайте также: