Solved

SQL Divide by Zero error

Posted on 2014-11-12
8
186 Views
Last Modified: 2014-11-12
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
Comment
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
  • Learn & ask questions
8 Comments
 
LVL 17

Expert Comment

by:OriNetworks
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

by:abarefoot
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

by:
Aneesh Retnakaran earned 250 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 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

Open in new window

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 33

Expert Comment

by:ste5an
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;

Open in new window


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

by:abarefoot
ID: 40438349
Case statement worked perfect for this.
0
 
LVL 65

Expert Comment

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

Author Comment

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

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question