Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# SQL Divide by Zero error

Posted on 2014-11-12
Medium Priority
196 Views
Not sure why I'm still getting this error.  See the section that giving me issues.  Thanks for your help.

(inventory_supplier.cost - CONVERT(INT,inventory_supplier.supplier_sort_code)) / CONVERT(INT,inventory_supplier.supplier_sort_code)
/ nullif ((inventory_supplier.cost - CONVERT(INT,inventory_supplier.supplier_sort_code)) / CONVERT(INT,inventory_supplier.supplier_sort_code) ,0)

as last_new_cost_diff
0
Question by:abarefoot
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 17

Expert Comment

ID: 40438158
If you use a simple select on those fields are any zero? Since you have multiple divisions you are trying removing some parts to see which parts of the expression are returning zero. My guess is this part
nullif ((inventory_supplier.cost - CONVERT(INT,inventory_supplier.supplier_sort_code)) / CONVERT(INT,inventory_supplier.supplier_sort_code) ,0)
0

LVL 1

Author Comment

ID: 40438215
The issue is some inventory_supplier.supplier_sort_code has 0.  The part your talking about was my attempt to fix the divide by zero issue.
0

LVL 75

Accepted Solution

Aneesh Retnakaran earned 1000 total points
ID: 40438288
You need a case statement ,  I ma not sure what result you need in case the inventory_supplier.supplier_sort_code is Zero, I assume the result to be 0

case when inventory_supplier.supplier_sort_code = 0  THEN 0 ELSE
(inventory_supplier.cost - CONVERT(INT,inventory_supplier.supplier_sort_code)) / CONVERT(INT,inventory_supplier.supplier_sort_code)
/ nullif ((inventory_supplier.cost - CONVERT(INT,inventory_supplier.supplier_sort_code)) / CONVERT(INT,inventory_supplier.supplier_sort_code) ,0)

END
0

LVL 66

Assisted Solution

Jim Horn earned 1000 total points
ID: 40438292
< without digging into your code, sorry, meeting coming up >
Anytime x / y creates a division by zero error, the way in SQL to handle it is using a CASE block

``````CASE WHEN y = 0 THEN 0 ELSE x / y END as column_name
``````
For a full understanding of CASE blocks please visit my article  SQL Server CASE Solutions, which has a wompload of code and images, if it helps.
0

LVL 35

Expert Comment

ID: 40438317
First of all: Use alias names and a CTE to make your statement better readable.

Second: Post a concise and complete example. This includes table DDL and sample data INSERT statements. Otherwise we can only guess. Posting a question without is imho annoying behavior.

``````WITH Converted AS
(
SELECT	IS.cost,
CONVERT(INT, IS.supplier_sort_code) AS supplier_sort_code,
IS.cost - CONVERT(INT, IS.supplier_sort_code) AS CostCostDiff
FROM	inventory_supplier IS
)
SELECT	C.CostCostDiff / C.supplier_sort_code / NULLIF(C.CostCostDiff / C.supplier_sort_code ,0) AS last_new_cost_diff
FROM	Converted C;
``````

Now you see that your using C.supplier_sort_code twice as divisor. So you need to handle this case. And as you're using C.CostCostDiff in the NULLIF clause, a 0 as this difference may propagate.

And last but not least: What are you trying to do?
0

LVL 1

Author Closing Comment

ID: 40438349
Case statement worked perfect for this.
0

LVL 66

Expert Comment

ID: 40438551
Thanks for the split.  Good luck with your project.  -Jim
0

LVL 1

Author Comment

ID: 40438561
Its only fair since they were posted practically the same time.  Thanks for the help!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages â€“ plus untold reputational damage to one of the worldâ€™s most trusted airlines. All due to a catastrophâ€¦
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this â€¦
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
###### Suggested Courses
Course of the Month7 days, 1 hour left to enroll