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 Monday, October 10, 2011 5:26 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Good question, I had to read it carefully.

http://brittcluff.blogspot.com/
Post #1187827
Posted Monday, October 10, 2011 5:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 1,262, Visits: 13,557
good question!!




rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1187836
Posted Monday, October 10, 2011 6:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 13, 2014 11:41 AM
Points: 1,393, Visits: 485
stewartc-708166 (10/10/2011)
This is a good question, showing that care needs to be exercised when working with NULLs

Ajay.Kedar (10/10/2011)
I tried and got wrong in first select case

But wanted to know that

How can we chack null in this case then?

As shown in the explanation, the way the CASE is structured will need to be changed somewhat, i.e.
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



This is the form that I always use nowadays, rarely are any of the select conditions that are simple value type check expressions.
Post #1187849
Posted Monday, October 10, 2011 6:12 AM
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: Wednesday, September 17, 2014 1:32 PM
Points: 581, Visits: 733
Great question.

Thanks,

Matt
Post #1187853
Posted Monday, October 10, 2011 6:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:03 AM
Points: 1,895, Visits: 2,194
Nice one! I almost missed it, but my spidy sense told me to look at the question again. Thanks.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1187859
Posted Monday, October 10, 2011 6:25 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:27 AM
Points: 3,302, Visits: 3,567
Nice question - thanks

-------------------------------
Posting Data Etiquette - Jeff Moden
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
Post #1187865
Posted Monday, October 10, 2011 7:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1187907
Posted Monday, October 10, 2011 7:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 21, 2014 9:08 AM
Points: 2,625, Visits: 1,056
I got an error when I executed the code - oh well
Post #1187909
Posted Monday, October 10, 2011 7:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:32 AM
Points: 1,805, Visits: 6,580
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'

Post #1187910
Posted Monday, October 10, 2011 7:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:32 AM
Points: 1,805, Visits: 6,580
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

Post #1187916
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse