case statement puzzle

  • Comments posted to this topic are about the item case statement puzzle

  • Nice question. I didn't know that constants (like 1) could be placed after CASE, so I definately learned something today.

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

  • Nice Question,:-)

    as you said once the condition is satisfied it will not look at other statement.

  • I really need to stop being so mistrusting. The right answer was so obvious, I assumed it had to be a trick question and got it wrong :pinch:.

  • Good question that in its simplicity makes you question what seems the obvious answer and think about it more deeply.

    According to SQL Books Online, the parser actually checks all conditions and returns the first true condition, if none then returns "else" if its coded otherwise NULL. That's how it seems to be described.

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • Really a nice question..

    I have got the confusion that, we need to declare the variable name followed by case statement and after that the value followed by when.

    Thanks..

  • This one reminds me of the fundamental concepts I learned in C. 0=false, 1=true.

  • da-zero (5/26/2010)


    Nice question. I didn't know that constants (like 1) could be placed after CASE, so I definately learned something today.

    Yeah, same here. Good thing a 4th choice like "error" wasn't supplied with the question or I might have gotten it wrong :exclamation:

  • Steve Jones - Editor (5/26/2010)


    This one reminds me of the fundamental concepts I learned in C. 0=false, 1=true.

    If you are saying that it chooses the first one because 1 = true, that is not correct. It is using the Case syntax similar to a Switch statement. The Select is equivalent to

    Select Case

    When @flg = 1 then 'True'

    When @flg = 1 then 'False'

    Else 'No Flag'

    Try changing the constant or the value of @flg. It will result in "No Flag"

  • Interesting. I did not know that a case could be written this way as well.

    Thanks.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • F1Droid (5/26/2010)


    Good question that in its simplicity makes you question what seems the obvious answer and think about it more deeply.

    According to SQL Books Online, the parser actually checks all conditions and returns the first true condition, if none then returns "else" if its coded otherwise NULL. That's how it seems to be described.

    Yeah, I had the right answer in 5 seconds, but took another 3 minutes to answer the question because I was sure it couldn't be that simple...:w00t:

    As for the Books Online reference, anyone know of a good way to introduce a condition with side-effects so we could verify if it does indeed check all conditions?

  • I was thinking it has to be true, but wait....... So I spent a few more minutes thinking, only to come to the same conclusion.

  • The BOL seems to imply the 1 in "CASE 1" might be interpreted as a bit constant. Is this true? It seems 0/1 values are bit constants.

    Note: It would not change the answer in this case due to the implicit conversion, but like others I was mistrusting and looking at it from all different angles. Just curious from an understanding standpoint.

  • Thanks for explaining the example clearly. I answered it correctly but through some different logic. after your explaination, I understand my mistake

  • The question was pretty simple.....! But I really liked it.

    Since the @flg is defined with value = 1 and it is matching 1 again in Case statement so result should be obviously 'true'.

    Nice question...keep posting more question.... 🙂

    {{{{

    Declare @flg as int

    SEt @flg = 1

    select case 1 when @flg then 'true'

    when @flg then 'false'

    else 'noflg'

    }}}}

    end as flag

    Thanks.

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

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