abarefoot
asked on
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_supp lier.suppl ier_sort_c ode)) / CONVERT(INT,inventory_supp lier.suppl ier_sort_c ode)
/ nullif ((inventory_supplier.cost - CONVERT(INT,inventory_supp lier.suppl ier_sort_c ode)) / CONVERT(INT,inventory_supp lier.suppl ier_sort_c ode) ,0)
as last_new_cost_diff
(inventory_supplier.cost - CONVERT(INT,inventory_supp
/ nullif ((inventory_supplier.cost - CONVERT(INT,inventory_supp
as last_new_cost_diff
ASKER
The issue is some inventory_supplier.supplie r_sort_cod e has 0. The part your talking about was my attempt to fix the divide by zero issue.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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?
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?
ASKER
Case statement worked perfect for this.
Thanks for the split. Good luck with your project. -Jim
ASKER
Its only fair since they were posted practically the same time. Thanks for the help!
nullif ((inventory_supplier.cost - CONVERT(INT,inventory_supp