SQL Divide by Zero error

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
LVL 1
abarefootAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OriNetworksCommented:
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
abarefootAuthor Commented:
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
Aneesh RetnakaranDatabase AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
< 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
ste5anSenior DeveloperCommented:
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
abarefootAuthor Commented:
Case statement worked perfect for this.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
abarefootAuthor Commented:
Its only fair since they were posted practically the same time.  Thanks for the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.