Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rsaver
Starting Member
4 Posts |
 Posted - 2013-02-20 : 10:17:13
|
| I've used Oracle for the past 5 years, but I have new position doing data extraction from SQL 2008. My position does not allow access down to the admin or t-sql level, so some things are beyond my reach. Here's the problem. I'm running a simple query on a 'code' table in our main application. It's not pulling the correct number of records based on my '=' vs '<>' criteria. Here's an example of what I'm seeing:SELECT COUNT(DISTINCT [my_code]) My_code_countfrommytable That produces the total number of distinct records in mytable which is 161. That is correct.SELECT COUNT(DISTINCT [my_code]) My_code_countfrommytablewhere obsolete = ‘Y’That produces the total number of obsolete records in mytable. The record count is 81, which is correct. 'obsolete' is a 1 character string field according to the documentation that I have.SELECT COUNT(DISTINCT [my_code]) My_code_countfrommytablewhere obsolete <> ‘Y’This should produce the total number of non-obsolete records. The correct number is 80, but it's returning 18 (?!?). When I change the where clause to ‘where obsolete is null’ it pulls 62 records…still not correct. But when I do ‘where obsolete <> ‘Y’ or obsolete is null’ it pulls 80…correct number. But that seems kinda goofy unless I’m missing something here.I'm see the same (incorrect) results using a data extraction utility built into the application as well as doing an excel query with odbc link. So it seems like a syntax problem in my query (which I doubt) or a database issue.I also used rtrim(ltrim(obsolete)) to trim any spaces...same incorrect results.This seems like programming 101...<> should produce the exact opposite of =. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-02-20 : 10:22:13
|
where obsolete <> 'Y' or obsolete is null looks good - what is the problem?also possible: where isnull(obsolete,'') <> 'Y'
Too old to Rock'n'Roll too young to die. |
 |
|
|
wided
Posting Yak Master
218 Posts |
Posted - 2013-02-20 : 10:22:15
|
| probably obsolete is null in some recordsadd thiswhere (obsolete <> ‘Y’ or obsolete is null) |
|
|
|
rsaver
Starting Member
4 Posts |
Posted - 2013-02-20 : 11:10:45
|
| Yes, I got 'obsolete <> 'Y' or obsolete is null' to work...but the question is why doesn't obsolete <> 'Y' produce the exact opposite of obsolete = 'Y'.I want to understand if this is a quirk in how SQL handles null values. I don't want to just 'patch' the query so it works, because this will affect how I approach things in the future. This goes against my 28 years of programming experience where <> ALWAYS produces the exact opposite of =. |
|
|
|
rsaver
Starting Member
4 Posts |
Posted - 2013-02-20 : 11:18:30
|
| Check out this page in the section about the where clausehttp://msdn.microsoft.com/en-us/library/bb264565(v=sql.90).aspxPer this page, it should work as I described. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-20 : 12:01:32
|
| The short answer is that NULL is neither equal to or not equal to anything else, it is simply UNKNOWN. Unknonw is different from NOT EQUAL. |
|
|
|
bhushan_juare
Starting Member
45 Posts |
Posted - 2013-02-21 : 07:39:13
|
| hi rsaver,the ans was in your code itself reason is you have to use this condition only to get correct count i.e. ‘where obsolete <> ‘Y’ OR obsolete IS NULL’Note:- Mark if ans is correct |
|
|
|
rsaver
Starting Member
4 Posts |
Posted - 2013-02-25 : 12:19:43
|
quote:
Originally posted by bhushan_juare
hi rsaver,the ans was in your code itself reason is you have to use this condition only to get correct count i.e. ‘where obsolete <> ‘Y’ OR obsolete IS NULL’Note:- Mark if ans is correct
Thanks everyone for your help. I'm taking this suggestion and moving on with it. |
|
|
|
|
|
|
|
|
Incorrect record counts or am I missing something?