Divide by 0 error

Transact-SQL
1

Hi, I must first let you know I'm not very good at SQL but hope someone can help.

I'm having an issue with SQL. The error I get is the following;
ODBC Error : [SQLState : 22012] [Driver Error 8134] [Microsoft][ODBC SQL Server Driver][SQL Server]Divide by zero error encountered. [SQLState : 37000] [Driver Error 16945] [Microsoft][ODBC SQL Server Driver][SQL Server]The cursor was not declared.

When looking on the SQL table there is the following command;
LineItems.ItemPrice / LineItems.QuantityOrdered AS itemcost,

I have checked all both ItemPrice and QuantityOrdered column and all have valid numbers however, I keep getting the following message when running the query above.
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

If I comment out the command above there is no issue however, I do need the price per unit.

The item price is to be divided by the quantity ordered to give a price per unit.

I hope someone can help or at least point me in the right direction.

Thanks
p4rma

2

LineItems.ItemPrice / CASE WHEN LineItems.QuantityOrdered = 0 THEN 1 ELSE LineItems.QuantityOrdered END AS itemcost,

1 Like
3

Wow ScottPletcher, thanks so much. This worked, I will try to look at what you've done to see if I can make sense of it.

4

I usually prefer:

LineItems.ItemPrice / NullIf(LineItems.QuantityOrdered, 0) AS itemcost

Which gives NULL if a divide by zero occurs rather than just returning the same amount as if 1 item had been order (and avoids a CASE statement). You can always ISNULL the result if you need a different response.

2 Likes
5

Here is the workaround of this SQL error 8134. Go through it.
https://blog.sqlauthority.com/2016/08/27/sql-server-fix-error-8134-divide-zero-error-encountered/

1 Like