SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CASE vagaries


CASE vagaries

Author
Message
Ol'SureHand
Ol'SureHand
SSC-Addicted
SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)

Group: General Forum Members
Points: 499 Visits: 720
Comments posted to this topic are about the item CASE vagaries
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10947 Visits: 11984
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27263 Visits: 13268
What a weird question :-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
david.gwalter
david.gwalter
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 18
First time I have come across a question with an incorrect answer.
A little disappointing for a novice like myself.
Keld Laursen (SEGES)
Keld Laursen (SEGES)
Old Hand
Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)

Group: General Forum Members
Points: 366 Visits: 202
[quote]Hugo Kornelis (9/7/2013)
<the truth> [quote]

+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
Danny Ocean
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1616 Visits: 1549
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
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10947 Visits: 11984
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
nenad-zivkovic
nenad-zivkovic
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1118 Visits: 780
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)
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8839 Visits: 7281
Hugo Kornelis (9/7/2013)
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 true (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 distractors 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).)


+1

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Michael Riemer
Michael Riemer
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2837 Visits: 638
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search