October 11, 2011 at 4:33 am
Simple question, thanks for the reminder.
-- Gianluca Sartori
October 11, 2011 at 12:55 pm
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.
October 11, 2011 at 2:19 pm
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.
October 11, 2011 at 10:50 pm
nice question , it depends on ansi_nulls settings
Statement 1 -
set ansi_nulls off
SELECT CASE COL1 WHEN NULL THEN 'Is Null'
WHEN '0' THEN 'Is Zero'
WHEN '1' THEN 'Is One'
WHEN '2' THEN 'Is Two'
END
FROM
(
SELECT NULL AS Col1
UNION
SELECT '0' AS Col1
UNION
SELECT '1' AS Col1
UNION
SELECT '2' AS Col1
) TMP
statement 2 -
set ansi_nulls on
SELECT CASE COL1 WHEN NULL THEN 'Is Null'
WHEN '0' THEN 'Is Zero'
WHEN '1' THEN 'Is One'
WHEN '2' THEN 'Is Two'
END
FROM
(
SELECT NULL AS Col1
UNION
SELECT '0' AS Col1
UNION
SELECT '1' AS Col1
UNION
SELECT '2' AS Col1
) TMP
Regards Deepak
October 12, 2011 at 9:43 am
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.
October 13, 2011 at 7:31 pm
unfortunately i got it wrong. But got something new:-)
October 13, 2011 at 11:43 pm
Good Question.Thanks
Malleswarareddy
I.T.Analyst
MCITP(70-451)
October 14, 2011 at 7:16 am
Thanks L' Eomot Inversé for pointing it out. I overlook this while putting the explanation.
October 21, 2011 at 6:40 pm
I wanted a non of the above answer since the single quotes would not print out.
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply