Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

CASE vagaries Expand / Collapse
Author
Message
Posted Saturday, September 7, 2013 4:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, June 21, 2014 6:12 AM
Points: 368, Visits: 715
Comments posted to this topic are about the item CASE vagaries
Post #1492569
Posted Saturday, September 7, 2013 5:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 5,930, Visits: 8,179
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
Post #1492571
Posted Sunday, September 8, 2013 1:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 13,334, Visits: 10,201
What a weird question



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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1492613
Posted Sunday, September 8, 2013 11:22 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:33 PM
Points: 51, Visits: 18
First time I have come across a question with an incorrect answer.
A little disappointing for a novice like myself.
Post #1492658
Posted Monday, September 9, 2013 12:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 9, 2014 6:09 AM
Points: 302, Visits: 177
[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.
Post #1492664
Posted Monday, September 9, 2013 12:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
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

Post #1492665
Posted Monday, September 9, 2013 12:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 5,930, Visits: 8,179
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
Post #1492668
Posted Monday, September 9, 2013 1:06 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 3:13 AM
Points: 636, Visits: 660
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)
Post #1492669
Posted Monday, September 9, 2013 1:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 3,864, Visits: 5,012
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”
Post #1492680
Posted Monday, September 9, 2013 2:28 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:40 AM
Points: 1,721, Visits: 470
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.
Post #1492688
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse