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

Output of Query Expand / Collapse
Author
Message
Posted Tuesday, October 11, 2011 4:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 5,019, Visits: 10,559
Simple question, thanks for the reminder.

--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1188322
Posted Tuesday, October 11, 2011 12:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 7, 2014 2:20 PM
Points: 24, Visits: 182
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

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:25 PM
Points: 347, Visits: 1,068
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

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 2, 2014 9:57 AM
Points: 554, 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
Post #1188864
Posted Wednesday, October 12, 2011 9:43 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 11, 2014 11:01 AM
Points: 868, Visits: 1,134
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

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:20 AM
Points: 58, Visits: 105
unfortunately i got it wrong. But got something new
Post #1190269
Posted Thursday, October 13, 2011 11:43 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
Good Question.Thanks

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #1190308
Posted Friday, October 14, 2011 7:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:34 PM
Points: 32, Visits: 59
I wanted a non of the above answer since the single quotes would not print out.
Post #1194744
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse