Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Output of Query Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, October 11, 2011 4:33 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Yesterday @ 10:00 AM Points: 4,902, Visits: 8,544
Post #1188322
 Posted Tuesday, October 11, 2011 12:55 PM
 Grasshopper Group: General Forum Members Last Login: Thursday, January 30, 2014 11:48 AM Points: 24, Visits: 174
 As the correct output is Null,'Is Zero', 'Is One', 'Is Two'.I want to know why NULL is also the part of the output.The explanation says that:The statement CASE COL1 WHEN NULL THEN 'Is Null' doesn't evaluate to TRUTH value as NULL represents MISSING value.Hence it returns NULL.As the statement CASE COL1 WHEN NULL THEN 'Is Null' is not evaluated to TRUTH,then in that case it should not return any values as there is no else condition mentioned in the statement.Kindly help me to understand this.
Post #1188713
 Posted Tuesday, October 11, 2011 2:19 PM
 Old Hand Group: General Forum Members Last Login: Friday, January 17, 2014 10:03 PM Points: 341, Visits: 1,025
 jigsm_shah (10/11/2011)As the correct output is Null,'Is Zero', 'Is One', 'Is Two'.I want to know why NULL is also the part of the output.The explanation says that:The statement CASE COL1 WHEN NULL THEN 'Is Null' doesn't evaluate to TRUTH value as NULL represents MISSING value.Hence it returns NULL.As the statement CASE COL1 WHEN NULL THEN 'Is Null' is not evaluated to TRUTH,then in that case it should not return any values as there is no else condition mentioned in the statement.Kindly help me to understand this.The answer is in the else condition -- if there's no else, then the result is null.You can verify this by adding an additional value in the tmp subquery, say 10 or 42), you'll get nulls for those rows.
Post #1188773
 Posted Tuesday, October 11, 2011 10:50 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Thursday, January 02, 2014 9:57 AM Points: 554, Visits: 863
 nice question , it depends on ansi_nulls settingsStatement 1 - set ansi_nulls offSELECT CASE COL1 WHEN NULL THEN 'Is Null' WHEN '0' THEN 'Is Zero' WHEN '1' THEN 'Is One' WHEN '2' THEN 'Is Two' ENDFROM( SELECT NULL AS Col1 UNION SELECT '0' AS Col1 UNION SELECT '1' AS Col1 UNION SELECT '2' AS Col1) TMPstatement 2 -set ansi_nulls onSELECT CASE COL1 WHEN NULL THEN 'Is Null' WHEN '0' THEN 'Is Zero' WHEN '1' THEN 'Is One' WHEN '2' THEN 'Is Two' ENDFROM( SELECT NULL AS Col1 UNION SELECT '0' AS Col1 UNION SELECT '1' AS Col1 UNION SELECT '2' AS Col1) TMPRegards Deepak
Post #1188864
 Posted Wednesday, October 12, 2011 9:43 AM
 SSC Eights! Group: General Forum Members Last Login: Yesterday @ 5:03 AM Points: 865, Visits: 1,084
 L' Eomot Inversé (10/10/2011)Good question.But the real answer of course is "it depends" on what the setting of ansi_nulls is; it's on by default in an SSMS query window, so the given answer is correct there; it is incorrect in a context where ansi_nulls is off, which is the default for connections where the application or the connection provider doesn't explicitly it in the connection dialog (SMSS does, SQL 2000 QA did, but some things don't). Fortunately the ansi_nulls setting is an issue which will go away - the ability to change this setting it is a deprecated feature, there are important features which don't work if it is off (indexed views, indexes on computed columns, query notifications), and there will be a new version of SQL Server in which ansi_nulls is effectively always on and can't be switched off.One more vote for Toms explanation here.
Post #1189262
 Posted Thursday, October 13, 2011 7:31 PM
 Valued Member Group: General Forum Members Last Login: 2 days ago @ 3:14 AM Points: 57, Visits: 95
 unfortunately i got it wrong. But got something new
Post #1190269
 Posted Thursday, October 13, 2011 11:43 PM
 SSCommitted Group: General Forum Members Last Login: Sunday, March 02, 2014 11:46 PM Points: 1,937, Visits: 1,147
 Good Question.Thanks MalleswarareddyI.T.AnalystMCITP(70-451)
Post #1190308
 Posted Friday, October 14, 2011 7:16 AM
 SSC Veteran Group: General Forum Members Last Login: Wednesday, September 18, 2013 6:40 AM Points: 253, Visits: 79
 Thanks L' Eomot Inversé for pointing it out. I overlook this while putting the explanation.
Post #1190528
 Posted Friday, October 21, 2011 6:40 PM
 SSC Rookie Group: General Forum Members Last Login: Monday, March 03, 2014 7:29 AM Points: 28, Visits: 36
 I wanted a non of the above answer since the single quotes would not print out.
Post #1194744

 Permissions