Sorting triggered by data type

  • Comments posted to this topic are about the item Sorting triggered by data type

  • Nice question learned something today - thanks

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • 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

  • 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.

  • Posted incompletely, see below for my comments

  • 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

  • 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

  • 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

  • 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.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • 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),


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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 🙂

  • 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.

  • This is valid code and the CONVERT will only fail when IsInsurance = 1 and Value <> Integer value.

    http://brittcluff.blogspot.com/

  • good question!!!

    hugo, very good explanation!!!!!

    thanks!!!!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply