| Author |
 Topic  |
|
|
rsaver
Starting Member
USA
4 Posts |
 Posted - 02/20/2013 : 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_count
from
mytable
That produces the total number of distinct records in mytable which is 161. That is correct.
SELECT
COUNT(DISTINCT [my_code]) My_code_count
from
mytable
where 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_count
from
mytable
where 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
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 02/20/2013 : 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
152 Posts |
Posted - 02/20/2013 : 10:22:15
|
probably obsolete is null in some records
add this
where (obsolete <> ‘Y’ or obsolete is null)
|
|
|
|
rsaver
Starting Member
USA
4 Posts |
Posted - 02/20/2013 : 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
USA
4 Posts |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3826 Posts |
Posted - 02/20/2013 : 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
44 Posts |
Posted - 02/21/2013 : 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
USA
4 Posts |
Posted - 02/25/2013 : 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. |
|
|
| |
Topic |
|
Incorrect record counts or am I missing something?
