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 9, 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: Thursday, July 23, 2015 2:40 AM Points: 1,242, Visits: 1,549
 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: Friday, July 22, 2016 6:45 AM Points: 205, Visits: 59
 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: Sunday, November 6, 2016 10:25 PM Points: 731, Visits: 2,150
 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: Friday, July 22, 2016 6:45 AM Points: 205, Visits: 59
 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
 SSCoach Group: General Forum Members Last Login: 2 days ago @ 2:09 AM Points: 15,501, Visits: 13,163
 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 SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1187756
 Posted Monday, October 10, 2011 2:16 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 11:35 AM Points: 9,829, Visits: 11,899
 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
Post #1187758
 Posted Monday, October 10, 2011 3:15 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 6:42 AM Points: 5,362, Visits: 7,071
 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
 SSCrazy Group: General Forum Members Last Login: Wednesday, November 30, 2016 2:06 AM Points: 2,251, Visits: 8,029
 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: Friday, July 22, 2016 6:45 AM Points: 205, Visits: 59
 great I am happy with this answer.Thankd stewartc-708166.
Post #1187776

 Permissions