Divide by zero error when using CASE statement
-
I have the following code: It returns a divide by zero error when oe_line.unit_price is 0. Apparently 2 case statements in the same formula does not work. If I separate them into 2 separate statements instead of using the divide by, it works. Since thisis part of an 'Insert" I'd rather have this work instead of using 2 fields. Any ideas?
Thanks.
CASE inventory_supplier.cost
when 0 then
0
else
(((100 * inventory_supplier.cost * 1.1) / (100.00000001 -
CASE oe_line.unit_price
WHEN 0 THEN
1 --avoid divide by 0 error
ELSE
(oe_line.unit_price - oe_line.commission_cost) / oe_line.unit_price * 100
END ) ) - (inventory_supplier.cost * 1.1))
/
((100 * inventory_supplier.cost * 1.1) / (100.00000001 -
CASE oe_line.unit_price
WHEN 0 THEN
1 --avoid divide by 0 error
ELSE
(oe_line.unit_price - oe_line.commission_cost) / oe_line.unit_price * 100 END ) ) * 100
end c_new_PP,
-
Try to use this
CREATE FUNCTION GPDivNErr(@Val1 float, @Val2 float)RETURNS float
AS
BEGIN
DECLARE @MyVal FLOAT
IF @Val2=0
SELECT @MyVal = 0
ELSE
SELECT @MyVal = @Val1/@Val2
RETURN @MyVal
END
--select dbo.GPDivNErr(2,2)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
Try using NULLIF, replace
(oe_line.unit_price - oe_line.commission_cost) / oe_line.unit_price * 100 END ) ) * 100
with
(oe_line.unit_price - oe_line.commission_cost) / NULLIF(oe_line.unit_price,0) * 100 END ) ) * 100
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
To add to Mark's reply, you have to use ISNULL with NULLIF to get that replacement of 0 to 1; like
ISNULL( NULLIF(oe_line.unit_price,0) , 1)Cheers!
-
The simplest way is to avoid the calculation altogether;
c_new_PP = CASE WHEN oe_line.unit_price = 0 OR inventory_supplier.cost = 0 THEN 0 ELSE(
(
(100 * inventory_supplier.cost * 1.1)
/ (
100.00000001 - (oe_line.unit_price - oe_line.commission_cost) / oe_line.unit_price * 100
)
)
- (inventory_supplier.cost * 1.1)
) /
(
(100 * inventory_supplier.cost * 1.1)
/ (
100.00000001 - (oe_line.unit_price - oe_line.commission_cost) / oe_line.unit_price * 100
)
)
* 100
END
The bracketing in the calculation looks dangerous to me - I'd check that this is providing you with the results you expect. This looks safer:
(100.00000001 - oe_line.unit_price - oe_line.commission_cost) / (oe_line.unit_price * 100)“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Thanks for the great solutions, but none of them worked. I'm going to take the numerator and make 1 field and the divisor another field in a temp table, then divide them to insert into the main table.
Unless there are other suggestions?
-
turkey-500673 (5/10/2010)
Thanks for the great solutions, but none of them worked.But why ? , Did you get stuck somewhere ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
turkey-500673 (5/10/2010)
Thanks for the great solutions, but none of them worked. I'm going to take the numerator and make 1 field and the divisor another field in a temp table, then divide them to insert into the main table.Unless there are other suggestions?
"The bracketing in the calculation looks dangerous to me - I'd check that this is providing you with the results you expect."
Can you post a sample table script with some sample data please? This is hardly rocket science.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Chris Morris-439714 (5/11/2010)
"The bracketing in the calculation looks dangerous to me - I'd check that this is providing you with the results you expect."Can you post a sample table script with some sample data please? This is hardly rocket science.
I definitely second Chris here.. The brackets looks very dangerous and very mis-leading. I tried pasting the code in SSMS and i found it hard to group the brackets..
Please post a clear code and as Chris requested, we need sample data and table scritps..
-
Your formula looks a bit strange, especially the place where you substructing 1.
You can try the following:
ISNULL(
(
(
(
100 * inventory_supplier.cost * 1.1
)
/
NULLIF(
(
100.00000001 -
ISNULL((
oe_line.unit_price - oe_line.commission_cost
)
/
NULLIF(oe_line.unit_price,0) * 100, 1) -- why 1, are you sure you want it here, as result will be 99.00000001?
), 0)
)
-
(
inventory_supplier.cost * 1.1
)
)
/
NULLIF(
(
(
100 * inventory_supplier.cost * 1.1
)
/
NULLIF(
(
100.00000001 -
ISNULL((
oe_line.unit_price - oe_line.commission_cost
)
/
NULLIF(oe_line.unit_price * 100, 0), 1) -- why 1, are you sure you want it here, as result will be 99.00000001?
), 0)
) * 100, 0)
, 0)
as Whatever
Could you supply the exact formula you need, without case statements?
Specify what you want your results to be in case of any devider is zero (eg. unit_price = 0 and 100.00000001 - (oe_line.unit_price - oe_line.commission_cost)/oe_line.unit_price,0 * 100 = 0, see here, your case statements do not check the case when this is = 0)
Test data would helpfull...
-
You guys are the best! The last post worked like a charm, not sure why other suggestions did not. You're absolutely right, the code is definitely klugy...you should see the rest of it. I inherited this after the previous programmer left very suddenly.
Now that it's working and the urgency is over I can spend some time looking at the brackets and why they have those multipliers.
Thanks again everyone. will probably be back with more questions.
-
You are welcome!
As I said in my post, each devider in your monster formula should be checked for zero value.
I have gut feeling that this spaggeti-formula can be significantly cut in size 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply