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


Output of Query


Output of Query

Author
Message
Gianluca Sartori
Gianluca Sartori
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: 10344 Visits: 13351
Simple question, thanks for the reminder.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
jignesh209
jignesh209
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 185
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.
john.moreno
john.moreno
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 1115
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.
deepak.a
deepak.a
Right there with Babe
Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)Right there with Babe (784 reputation)

Group: General Forum Members
Points: 784 Visits: 863
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
Carlton Leach
Carlton Leach
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: 1086 Visits: 1296
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.
jayant.m.1
jayant.m.1
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 136
unfortunately i got it wrong. But got something new:-)
malleswarareddy_m
malleswarareddy_m
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2235 Visits: 1189
Good Question.Thanks

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Sunil Chandurkar
Sunil Chandurkar
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 80
Thanks L' Eomot Inversé for pointing it out. I overlook this while putting the explanation.
annjunk
annjunk
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 77
I wanted a non of the above answer since the single quotes would not print out.
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