July 11, 2002 at 9:30 am
Hey-
If I have this and @TotalAmt = 0:
CASE @TotalAmt
WHEN 0 THEN 0
ELSE 'X'
END AS APercent
I Get 0
- HOWEVER -
If I have this and @TotalAmt = 0: (exact same thing)
CASE @TotalAmt
WHEN 0 THEN 0
ELSE ROUND(SUM(CAST(ISNULL(ValueAmt, 0) AS DECIMAL) / @TotalAmt) * 100, 2)
END AS APercent
I get a divide by zero error !!!!!!
Why is it even looking at the 'ELSE' part of this ??????
I really dislike this TSQL stuff - can't wait for C# to be implemented.
July 11, 2002 at 9:45 am
Your problem looks like @TotalAmt is a char type datatype. And the case the first time implicitly converts since 'X' is char type then 0 is interpretted as a char datatype (char is any character type for this discussion). However in the second it does the implicit late it treats the 0 in the CASE as numeric since the math work in the else statement will result in a numeric. Unfortunately if our @TotalAmt is a char type then '0' does not = 0 for SQL in this case so it moves to the else and then does the implicit conversion so thus divide by 0. Try changing the datatype of @TotalAmt or explicitly cast to an int datatype in the WHEN portion of the the CASE statement and see what happens.
And yes this does look like a bug but implicite conversion is not always handled, explicite handlin is always best.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 07/11/2002 09:47:35 AM
July 11, 2002 at 9:52 am
Thanks but no cigar.
@TotalAmt is in fact a decimal(38,3)
So - the WHEN 0 part DOES fire !
Replace:
CASE @TotalAmt
WHEN 0 THEN 0
ELSE 'X'
END AS APercent
- WITH -
CASE @TotalAmt
WHEN 0 THEN 0
ELSE 9999
END AS APercent
To avoid confusion. In this case - I get 0.
The problem here is that the CASE statement falls through to the divide statement.
Actually - with further testing - the SUM portion seems to be problematic.
If I try:
CASE @TotalAmt
WHEN 0 THEN 123
ELSE 1 / 0
END AS APercent
I Get 123
The problem arises when I add the SUM to the equation.
Either way - why is it evaluating the ELSE part !!!!
July 11, 2002 at 10:09 am
To clarify even further:
This works:
CASE @TotalAmt
WHEN 0 THEN 9999
ELSE (CAST(ISNULL(ValueAmt, 0) AS DECIMAL) / @TotalAmt) * 100
END AS APercent
I Get 9999
- HOWEVER -
If I have this and @TotalAmt = 0: (exact same thing)
CASE @TotalAmt
WHEN 0 THEN 9999
ELSE ROUND(SUM(CAST(ISNULL(ValueAmt, 0) AS DECIMAL) / @TotalAmt) * 100, 2)
END AS APercent
I get the Divide by zero error.
WACKY !!!!!!!
July 11, 2002 at 12:17 pm
I don't know why you get the divide by zero error since the ELSE shouldn't get considered. However here is a possible solution:
SELECT
CASE
WHEN @TotalAmt = 0 THEN 9999
WHEN @TotalAmt <> 0 THEN ROUND(SUM(CAST(ISNULL(ValueAmt, 0) AS DECIMAL) / replace ('1','0',@TotalAmt)) * 100, 2)
END AS APercent
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
July 11, 2002 at 1:11 pm
Thanks rmarda - but still no go.
You state "since the ELSE shouldn't get considered." - that is still the problem.
If you notice in my last post - the problem arises ONLY when the aggregate SUM is used.
I feel that this is a T-SQL bug and I can reproduce the behaviour every time. Try it yourself. This is EASILY understandable and works every time !!!!!
--THIS Fails
SELECT
'A',
CASE (2 - 2)
WHEN 0 then 0
ELSE SUM(1 / 0)
END
--THIS Works
SELECT
'A',
CASE (2 - 2)
WHEN 0 then 0
ELSE 1 / 0
END
Tell me this is not a bug - please - give me a solution and prove me wrong !!!!
July 11, 2002 at 1:15 pm
I am not disputing the fact that this looks like a bug, I was simply showing you a way to get around the bug and make your case statement work.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
July 11, 2002 at 1:23 pm
rmarda-
Sorry - did not mean to offend you at all - I do appreciate your help.
I'm simply frustrated w/ T-SQL and would like other people to try this simple test and see if they get the same problem. Please - give it a try - and thanks again.
--THIS Fails
SELECT
'A',
CASE (2 - 2)
WHEN 0 then 0
ELSE SUM(1 / 0)
END
--THIS Works
SELECT
'A',
CASE (2 - 2)
WHEN 0 then 0
ELSE 1 / 0
END
July 11, 2002 at 1:36 pm
I quickly ran your examples with identical results - interesting to say the least. Unfortunately I don't have the time to investigate further.
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
July 11, 2002 at 1:36 pm
You have not offeneded me. I just wanted to clarify the intent of my first post. I see your frustration. The second when section will not get executed in both examples and so you would expect to not get a divide by zero error since the second when only gets executed when the divisor is not zero.
Since you only get this problem when you use the SUM function it would appear that this function is evaluated regardless of whether the when section will actually get executed. It probably has to do with the way SQL Server evaluates the CASE statement.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
July 11, 2002 at 3:58 pm
Drop Table TestII
GO
CREATE TABLE [dbo].[TestII] (
[Ext_Price] [Decimal](28, 3) NULL ,
[Measure] [Decimal](28, 3) NULL)
GO
Insert TestII Values(.500,1.000)
Insert TestII Values(.600,1.000)
Insert TestII Values(1.000,1.000)
GO
Select Sum(Case When IsNull(Measure,0)*IsNull(Ext_Price,0)=0 Then 0
Else Round(Ext_Price/Measure,2) End)*100.000
From TestII
"measure" = your @TotalAmt
"ext_price" = your ValueAmt
July 12, 2002 at 5:57 am
I noted when test that SUM(codehere) seems to be parsed before the case is. I would report as a bug to Microsoft. They may be aware of this but always helps to express dissatisfaction.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply