December 12, 2011 at 9:04 pm
Comments posted to this topic are about the item Sorting triggered by data type
December 12, 2011 at 9:05 pm
December 12, 2011 at 11:46 pm
Bit of a trick question - as it has nothing to do with sorting - but learned something. Thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 13, 2011 at 12:25 am
Interesting question, but the code itself is valid. The answer you classified as correct depends on the actual data. It appears reasonable to imply that c.IsInsuranceLength is set to 1 when cc.Value is a number. Then the query fails with inconsistent data. On the other hand, with consistent data, option 3 would be correct.
December 13, 2011 at 1:27 am
Posted incompletely, see below for my comments
December 13, 2011 at 1:31 am
ma-516002 (12/13/2011)
Interesting question, but the code itself is valid. The answer you classified as correct depends on the actual data. It appears reasonable to imply that c.IsInsuranceLength is set to 1 when cc.Value is a number. Then the query fails with inconsistent data. On the other hand, with consistent data, option 3 would be correct.
That's the point of the question, as stated in the explanation (fyi, I did the same mistake as you):
"The convert function is going to fail because in CASE statements all possibilites are being evaluated for each line."
As shown by this example:
declare @test-2 table(isint bit, val varchar(1))
insert @test-2
(isint, val)
values(1, '1')
insert @test-2
(isint, val)
values(0, 'a')
select case
when isint = 1 then convert(int, val)
else val
end
from @test-2 t
/@devandreas
December 13, 2011 at 1:36 am
I think that the explaination is actually slightly wrong.
ORDER BY
(CASE WHEN c.IsInsuranceLength=1 THEN CONVERT(INT, cc.Value )
ELSE cc.Value
END)
This statement does one of 2 things:
- Explicitly attempt to convert cc.Value to an INT if c.IsInsuranceLength=1
- Return the VARCHAR value of cc.Value otherwise
If c.IsInsuranceLength=1 and cc.Value is not a string representing an integer, then the explicit conversion will fail.
If every time c.IsInsuranceLength=1 the string represents an integer, this statement will work. However, ORDER BY requires compatible data types to sort with, so will do an implict conversion of the VARCHAR and INT datatypes to the higher data type, which in this case is INT. During this conversion, any values of cc.Value which do not represent an integer will cause this implicit conversion to fail.
Therefore, as the poster stated
Facts are that cc.[Value] is of VARCHAR data type and it contains both integer and non-integer kind of data.
it is not possible for the code to run successfully.
Philip
December 13, 2011 at 1:39 am
I should have thought this phrase has a purpose there:
Facts are that cc.[Value] is of VARCHAR data type and it contains both integer and non-integer kind of data.
Thanks for the question,
Iulian
December 13, 2011 at 2:38 am
I got the points because I made the correct assumption, but I think that if the data is consistent in representing an INT in "Value" where ever "IsInsuranceLength" = 1 then it'd work.
December 13, 2011 at 3:44 am
A nice question, but a completely incorrect explanation!
The problem is not that each possibility is evaluated for each line. That's easy to check, just run:
SELECT CASE WHEN i <> 0 THEN 5/i ELSE 5 END FROM (SELECT 0 AS i UNION ALL SELECT 1) AS d;
The actual reason that the statement fails, is that for a CASE expression that attempts to return multiple data types, all data types will eventually be implicitly converted to one data type, as defined by the rules of data type precedence. In this case, possible return types from the various THEN and ELSE clauses are int and varchar; int has the higher priority, so the varchar value will be implicitly converted to int. And that's where it fails.
Another way to verify this would be to change the CASE expression to
CASE WHEN c.IsInsuranceLength=1 THEN CONVERT(INT, cc.Value ) ELSE 0 END
If the explanation is correct that CASE expressions (not statements!!) are evaluated line by line, this would still fail. But if you run it, you'll see it won't. (Well, it might still fail, but if IsInsuranceLength = 1 is a correct indicator of int-convertable values in cc.Value, it won't),
December 13, 2011 at 3:45 am
Cadavre (12/13/2011)
I got the points because I made the correct assumption, but I think that if the data is consistent in representing an INT in "Value" where ever "IsInsuranceLength" = 1 then it'd work.
No, it would still fail, because in the end ALL values are converted to int. See my previous post.
December 13, 2011 at 4:20 am
Thank you very much for the clarification. My question was inspired by real issue and final solution let me to assume what I've posted. I am glad that MVP means something 🙂
December 13, 2011 at 5:30 am
Not only is the explanation incorrect, the answer given as the correct one is in fact also incorrect, since it is not the (explicit) CONVERT function that fails but the implicit conversion.
December 13, 2011 at 5:32 am
This is valid code and the CONVERT will only fail when IsInsurance = 1 and Value <> Integer value.
http://brittcluff.blogspot.com/
December 13, 2011 at 5:50 am
good question!!!
hugo, very good explanation!!!!!
thanks!!!!
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply