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
Solved

SQL Divide by Zero error

Posted on 2014-11-12
8
184 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

856 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