Solved

SQL Divide by Zero error

Posted on 2014-11-12
8
178 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

912 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now