Output of Query

Question

Output of Query

Viewing 15 posts - 16 through 30 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

Nice question - thanks

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

sqlzealot-81 (10/10/2011)
I guess, you can achieve by the below way,
SELECT
CASE IsNull(COL1,'') WHEN '' 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

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.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

BarbW SSCarpal Tunnel Points: 4759 More actions · Answer 3

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

Toreador SSChampion Points: 11365 More actions · Answer 4

Sean Lange (10/10/2011)[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.

If it matters then you can do something like

CASE IsNull(COL1,'avaluethatwillnotappearinthedata') WHEN 'avaluethatwillnotappearinthedata' THEN 'Is Null'

Toreador SSChampion Points: 11365 More actions · Answer 5

Or:

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'

END

end

FROM

(

SELECT NULL AS Col1

UNION

SELECT '0' AS Col1

UNION

SELECT '1' AS Col1

UNION

SELECT '2' AS Col1

) TMP

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sqlSherwin SSCrazy Points: 2884 More actions · Answer 7

Good question, luckily read the question twice before answering.

Aaron N. Cutshall SSCrazy Eights Points: 9009 More actions · Answer 8

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

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

Revenant SSC-Forever Points: 42467 More actions · Answer 9

Revenant

SSC-Forever

Points: 42467

October 10, 2011 at 9:13 am

#1392464

Simple but neat -- thank you!

Ken SSCoach Points: 17191 More actions · Answer 10

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

James_B SSCrazy Points: 2163 More actions · Answer 11

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".

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 12

OzYbOi d(-_-)b

SSCarpal Tunnel

Points: 4054

October 10, 2011 at 10:35 am

#1392512

good question - tks

john.moreno Default port Points: 1485 More actions · Answer 13

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.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 14

Thanks for the question. It was a good caffination check this morning. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 15

Excellent question. Thanks!

Rob Schripsema
Propack, Inc.