Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Output of Query Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, October 09, 2011 9:54 PM
 SSC Veteran Group: General Forum Members Last Login: Wednesday, September 18, 2013 6:40 AM Points: 253, Visits: 79
 Comments posted to this topic are about the item Output of Query
Post #1187712
 Posted Monday, October 10, 2011 12:03 AM
 Ten Centuries Group: General Forum Members Last Login: 2 days ago @ 2:28 AM Points: 1,239, Visits: 1,541
 Really good question. unfortunately i got it wrong. But got something new.Thanks ThanksVinay Kumar-----------------------------------------------------------------Keep Learning - Keep Growing !!!www.GrowWithSql.com
Post #1187727
 Posted Monday, October 10, 2011 12:14 AM
 SSC Veteran Group: General Forum Members Last Login: Wednesday, March 27, 2013 3:17 AM Points: 200, Visits: 58
 I tried and got wrong in first select case But wanted to know thatHow can we chack null in this case then?
Post #1187728
 Posted Monday, October 10, 2011 12:31 AM
 Right there with Babe Group: General Forum Members Last Login: Today @ 4:39 AM Points: 731, Visits: 1,851
 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' ENDFROM( SELECT NULL AS Col1 UNION SELECT '0' AS Col1 UNION SELECT '1' AS Col1 UNION SELECT '2' AS Col1) TMP`
Post #1187736
 Posted Monday, October 10, 2011 1:31 AM
 SSC Veteran Group: General Forum Members Last Login: Wednesday, March 27, 2013 3:17 AM Points: 200, Visits: 58
 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'.
Post #1187744
 Posted Monday, October 10, 2011 2:06 AM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 1:29 PM Points: 11,189, Visits: 8,033
 Nice question, thanks! How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at LessThanDot. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1187756
 Posted Monday, October 10, 2011 2:16 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 12:20 PM Points: 7,931, Visits: 8,348
 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. Tom'S iomadh doigh a th’ air cu a mharbhadh gun a thachdadh le ìme
Post #1187758
 Posted Monday, October 10, 2011 3:15 AM
 Hall of Fame Group: General Forum Members Last Login: Today @ 7:04 AM Points: 3,439, Visits: 4,579
 This is a good question, showing that care needs to be exercised when working with NULLsAjay.Kedar (10/10/2011)I tried and got wrong in first select case But wanted to know thatHow 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` ____________________________________________Space, the final frontier? not any more...All limits henceforth are self-imposed.“libera tute vulgaris ex”
Post #1187773
 Posted Monday, October 10, 2011 3:19 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 4:09 AM Points: 1,541, Visits: 5,608
 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
Post #1187775
 Posted Monday, October 10, 2011 3:20 AM
 SSC Veteran Group: General Forum Members Last Login: Wednesday, March 27, 2013 3:17 AM Points: 200, Visits: 58
 great I am happy with this answer.Thankd stewartc-708166.
Post #1187776

 Permissions