CASE vagaries

  • Ol'SureHand

    SSCrazy

    Points: 2742

    Comments posted to this topic are about the item CASE vagaries

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    The question is strange, and the answer is wrong.

    "If an ELSE path is not provided in a CASE statement, the value it returns is NULL, not the initial value of the column or expression."

    Absolutely tryue (and not vague at all). But not proven by this question. Since @x was not initialized before the SET with the CASE expression, the initial value was NULL, and the code does not enable us to see that it is actually set to NULL and not left unchanged.

    The distracters are weird. The last option (NULL) is the only one that at least makes some sense. The second option (1) would have been more believable if it was presented as "@X is now 1<". As for the first option - where would this p come from? I have no idea what reasoning could lead people to believe that this answer can be correct.

    And the actual answer is "@X is now A<". That's because the author uses ISNULL instead of the recommended COALESCE. ISNULL does not follow the normal data type precedence rules, but always returns the data type of the first argument. In this case, @X is the first argument, and @X is char(1) - so the result of ISNULL is 'A' ('A NULL' converted / truncated to char(1).)


    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/

  • Koen Verbeeck

    SSC Guru

    Points: 258888

    What a weird question 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • david.gwalter

    Valued Member

    Points: 59

    First time I have come across a question with an incorrect answer.

    A little disappointing for a novice like myself.

  • Keld Laursen (SEGES)

    SSC Eights!

    Points: 838

    Hugo Kornelis (9/7/2013)


    <the truth>

    +1

    Also: I didn't have any trouble ending up with NULL in @X. As Hugo states, but in other words:

    If you set a variable, you have to set it to something. Code with unfilled paths will lead to NULL values.

    The thingy about ISNULL() have bitten me before, so I knew what to expect. In fact, I thought that were the caveat to look for in todays question, så I were somewhat miffed when that answer were missing.

  • Danny Ocean

    SSCertifiable

    Points: 6098

    Hugo Kornelis (9/7/2013)The actual answer is "@X is now A<". That's because the author uses ISNULL instead of the recommended COALESCE. ISNULL does not follow the normal data type precedence rules, but always returns the data type of the first argument. In this case, @X is the first argument, and @X is char(1) - so the result of ISNULL is 'A' ('A NULL' converted / truncated to char(1).)

    +1

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!
    www.GrowWithSql.com

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    david.gwalter (9/8/2013)


    First time I have come across a question with an incorrect answer.

    A little disappointing for a novice like myself.

    Look at it from the popsitive side - at least the official correct answer was still the "least incorrect" option.

    (There have also been questions where the "least incorrect", or sometimes even the "actually correct" option were considered wrong. Most of these will usually be corrected when Steve catches the discussion, so you might not see them f you check old questions now).

    Bottom line - if you look at QotD as a way to learn, then always checkk the discussion on the boards!


    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/

  • nenad-zivkovic

    Default port

    Points: 1446

    Hugo Kornelis (9/7/2013)


    The question is strange, and the answer is wrong.

    As for the first option - where would this p come from? I have no idea what reasoning could lead people to believe that this answer can be correct.

    My guess would be that there is some line like SET @x = 'p' missing before the CASE, which would also explain the explanation.

    _______________________________________________
    www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)

  • This was removed by the editor as SPAM

  • Michael Riemer

    SSCarpal Tunnel

    Points: 4754

    Very strange question indeed - given that you can only end up with either of two answers, and then the fact that it caters for NULL, you can eliminate that - but still not get an available answer!

    Would have been nice to understand the full intention of the question as there are things to learn, as Hugo has explained - Thanks Hugo.

  • akondok

    Valued Member

    Points: 51

    DECLARE @x CHAR(1)

    SET @x = CASE

    WHEN @x = 'x' THEN 1

    END

    PRINT '@X is now ' + CAST(ISNULL(@x, 'A NULL') AS VARCHAR(50)) + '<'

    The correct answer should be @X is now B<

    This is because the value of @x after the SET statement is NULL. The ISNULL function attempts to assign the value 'A NULL' to @x but @x is CHAR(1) thus it is truncated to 'A'

  • Carlo Romagnano

    SSC-Insane

    Points: 21690

    Maybe, the author exchanged the CAST and the ISNULL:

    The right version:

    DECLARE @x CHAR(1)

    SET @x = CASE

    WHEN @x = 'x' THEN 1

    END

    PRINT '@X is now ' + ISNULL(CAST(@x AS VARCHAR(50)), 'A NULL') + '<'

  • batgirl

    SSCarpal Tunnel

    Points: 4979

    I hate it when we don't have a "none of the above" option.

  • sknox

    SSChampion

    Points: 12204

    david.gwalter (9/8/2013)


    First time I have come across a question with an incorrect answer.

    A little disappointing for a novice like myself.

    I find that even for questions that are well and correctly written, the discussion holds additional information. So it's almost always worthwhile to read the discussion, even if the question is poorly written. I treat the questions as more of an introduction to a topic of the day.

    I also try to keep in mind that the questions are written by the community, and so will reflect the varying levels of knowledge and skill out there.

    Having said that, question authors should at least run the code they write and make sure that their answer is correct in that respect.

  • james.a.waage

    SSC Journeyman

    Points: 78

    The correct answer is not among the choices...

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

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