SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sunil Chandurkar / Output of Query / Latest Posts

RE: Output of Query

annjunk — Fri, 21 Oct 2011 18:40:08 GMT

I wanted a non of the above answer since the single quotes would not print out.

RE: Output of Query

Sunil Chandurkar — Fri, 14 Oct 2011 07:16:58 GMT

Thanks L' Eomot Inversé for pointing it out. I overlook this while putting the explanation.

RE: Output of Query

malleswarareddy_m — Thu, 13 Oct 2011 23:43:19 GMT

Good Question.Thanks

RE: Output of Query

jayant.m.1 — Thu, 13 Oct 2011 19:31:29 GMT

unfortunately i got it wrong. But got something new:-)

RE: Output of Query

Carlton Leach — Wed, 12 Oct 2011 09:43:06 GMT

[quote][b]L' Eomot Inversé (10/10/2011)[/b][hr]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.[/quote]One more vote for Toms explanation here.

RE: Output of Query

deepak.a — Tue, 11 Oct 2011 22:50:19 GMT

nice question , it depends on [b]ansi_nulls[/b] 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

RE: Output of Query

john.moreno — Tue, 11 Oct 2011 14:19:22 GMT

[quote][b]jigsm_shah (10/11/2011)[/b][hr]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.[/quote]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.

RE: Output of Query

jerry209 — Tue, 11 Oct 2011 12:55:12 GMT

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.

RE: Output of Query

spaghettidba — Tue, 11 Oct 2011 04:33:20 GMT

Simple question, thanks for the reminder.

RE: Output of Query

Rob Schripsema — Mon, 10 Oct 2011 14:49:17 GMT

Excellent question. Thanks!

RE: Output of Query

Evil Kraig F — Mon, 10 Oct 2011 14:15:43 GMT

Thanks for the question. It was a good caffination check this morning. :-)

RE: Output of Query

john.moreno — Mon, 10 Oct 2011 11:00:17 GMT

I'd like to suggest a modification of the answer. What is happening is that the "CASE COLUMN WHEN" syntax does an equity comparision and then uses the result of that comparision, which of course fails with NULL, while the "CASE WHEN x" syntax uses the result of x. Which is why the given query returns null.So, something like:The CASE's statement has two alternative syntaxs, "CASE ColumnName WHEN Value" does an equity comparision between the column and the given value. This fails with NULLS unless ANSI_NULLS is off. The other syntax is "CASE WHEN EXPRESSION", which evalutes an expression (which may use any available columns) and uses the result of that expression to determine whether the condition has been met and the associated THEN used.

RE: Output of Query

OzYbOi d(-_-)b — Mon, 10 Oct 2011 10:35:29 GMT

good question - tks

RE: Output of Query

James_B — Mon, 10 Oct 2011 10:30:37 GMT

Great question - unfortunately I got it wrong.I thought it was too simple so I over compensated and assumed that the code would have "set ansi_nulls off" for the session since it is effectively saying "where col1 = null" rather than "where col1 is null".

RE: Output of Query

KWymore — Mon, 10 Oct 2011 10:03:34 GMT

Nice question about something that comes up regularly for me. Especially in writing queries for reporting.

RE: Output of Query

Revenant — Mon, 10 Oct 2011 09:13:50 GMT

Simple but neat -- thank you!

RE: Output of Query

Aaron N. Cutshall — Mon, 10 Oct 2011 08:45:07 GMT

Bugger! I hate it when I know the right answer but click on the wrong choice! :angry:

RE: Output of Query

Sherwin Anderson — Mon, 10 Oct 2011 08:19:11 GMT

Good question, luckily read the question twice before answering.

RE: Output of Query

SQLRNNR — Mon, 10 Oct 2011 08:19:04 GMT

Thanks for the question.

RE: Output of Query

Toreador — Mon, 10 Oct 2011 07:51:06 GMT

Or:[code="sql"]SELECT CASE when Col1 is null then 'Is Null'else case col1 WHEN '0' THEN 'Is Zero' WHEN '1' THEN 'Is One' WHEN '2' THEN 'Is Two' ENDendFROM( SELECT NULL AS Col1 UNION SELECT '0' AS Col1 UNION SELECT '1' AS Col1 UNION SELECT '2' AS Col1) TMP[/code]

RE: Output of Query

Toreador — Mon, 10 Oct 2011 07:39:52 GMT

[quote][b]Sean Lange (10/10/2011)[/b][hrThis works except you can't differentiate between null and empty strings because your isnull will now treat them both the same. In some cases this is ok but in others it isn't.[/quote]If it matters then you can do something likeCASE IsNull(COL1,'avaluethatwillnotappearinthedata') WHEN 'avaluethatwillnotappearinthedata' THEN 'Is Null'

RE: Output of Query

BarbW — Mon, 10 Oct 2011 07:36:32 GMT

I got an error when I executed the code - oh well :hehe:

RE: Output of Query

Sean Lange — Mon, 10 Oct 2011 07:34:01 GMT

[quote][b]sqlzealot-81 (10/10/2011)[/b][hr]I guess, you can achieve by the below way,[code="sql"]SELECT CASE IsNull(COL1,'') WHEN '' 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) TMP[/code][/quote]This works except you can't differentiate between null and empty strings because your isnull will now treat them both the same. In some cases this is ok but in others it isn't.

RE: Output of Query

Stuart Davies — Mon, 10 Oct 2011 06:25:08 GMT

Nice question - thanks

RE: Output of Query

Thomas Abraham — Mon, 10 Oct 2011 06:17:04 GMT

Nice one! I almost missed it, but my spidy sense told me to look at the question again. Thanks.

RE: Output of Query

Mattrick — Mon, 10 Oct 2011 06:12:43 GMT

Great question.Thanks,Matt

RE: Output of Query

mbova407 — Mon, 10 Oct 2011 06:11:29 GMT

[quote][b]stewartc-708166 (10/10/2011)[/b][hr]This is a good question, showing that care needs to be exercised when working with NULLs[quote][b]Ajay.Kedar (10/10/2011)[/b][hr]I tried and got wrong in first select case :-)But wanted to know thatHow can we chack null in this case then?[/quote]As shown in the explanation, the way the CASE is structured will need to be changed somewhat, i.e.[code="sql"]SELECT CASE WHEN COL1 IS NULL THEN 'Is Null' WHEN COL1 = '0' THEN 'Is Zero' WHEN COL1 = '1' THEN 'Is One' WHEN COL1 = '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[/code][/quote]This is the form that I always use nowadays, rarely are any of the select conditions that are simple value type check expressions.

RE: Output of Query

rfr.ferrari — Mon, 10 Oct 2011 05:41:17 GMT

good question!!:-)

RE: Output of Query

Britt Cluff — Mon, 10 Oct 2011 05:26:41 GMT

Good question, I had to read it carefully.

RE: Output of Query

Ajay.Kedar — Mon, 10 Oct 2011 03:20:25 GMT

great :-)I am happy with this answer.Thankd stewartc-708166.

RE: Output of Query

Toreador — Mon, 10 Oct 2011 03:19:59 GMT

I've a feeling the behaviour was different in a previous version of SQLServer, as I think we were bitten by it when code that worked, stopped working. I don't have anything pre-2005 to test on though, so I may well be getting confused with something else :-)

RE: Output of Query

Stewart "Arturius" Campbell — Mon, 10 Oct 2011 03:15:53 GMT

This is a good question, showing that care needs to be exercised when working with NULLs[quote][b]Ajay.Kedar (10/10/2011)[/b][hr]I tried and got wrong in first select case :-)But wanted to know thatHow can we chack null in this case then?[/quote]As shown in the explanation, the way the CASE is structured will need to be changed somewhat, i.e.[code="sql"]SELECT CASE WHEN COL1 IS NULL THEN 'Is Null' WHEN COL1 = '0' THEN 'Is Zero' WHEN COL1 = '1' THEN 'Is One' WHEN COL1 = '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[/code]

RE: Output of Query

L' Eomot Inversé — Mon, 10 Oct 2011 02:16:34 GMT

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.

RE: Output of Query

Koen Verbeeck — Mon, 10 Oct 2011 02:06:57 GMT

Nice question, thanks!

RE: Output of Query

Ajay.Kedar — Mon, 10 Oct 2011 01:31:25 GMT

Thank you very much for your reply.Yes, considering this example, it will work fine.But what if I already have space in Col1.For that value too, it will show me 'Is null'.

RE: Output of Query

sqlzealot-81 — Mon, 10 Oct 2011 00:31:04 GMT

I guess, you can achieve by the below way,[code="sql"]SELECT CASE IsNull(COL1,'') WHEN '' 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) TMP[/code]

RE: Output of Query

Ajay.Kedar — Mon, 10 Oct 2011 00:14:27 GMT

I tried and got wrong in first select case :-)But wanted to know thatHow can we chack null in this case then?

RE: Output of Query

Danny Ocean — Mon, 10 Oct 2011 00:03:28 GMT

Really good question. unfortunately i got it wrong. But got something new.Thanks:-)

Output of Query

Sunil Chandurkar — Sun, 09 Oct 2011 21:54:05 GMT

Comments posted to this topic are about the item [B]Output of Query[/B]