SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Incorrect record counts or am I missing something?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rsaver
Starting Member

USA
4 Posts

Posted - 02/20/2013 :  10:17:13  Show Profile  Reply with Quote
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
8760 Posts

Posted - 02/20/2013 :  10:22:13  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

wided
Posting Yak Master

199 Posts

Posted - 02/20/2013 :  10:22:15  Show Profile  Reply with Quote
probably obsolete is null in some records

add this

where (obsolete <> ‘Y’ or obsolete is null)
Go to Top of Page

rsaver
Starting Member

USA
4 Posts

Posted - 02/20/2013 :  11:10:45  Show Profile  Reply with Quote
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 =.
Go to Top of Page

rsaver
Starting Member

USA
4 Posts

Posted - 02/20/2013 :  11:18:30  Show Profile  Reply with Quote
Check out this page in the section about the where clause

http://msdn.microsoft.com/en-us/library/bb264565(v=sql.90).aspx

Per this page, it should work as I described.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 02/20/2013 :  12:01:32  Show Profile  Reply with Quote
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.
Go to Top of Page

bhushan_juare
Starting Member

45 Posts

Posted - 02/21/2013 :  07:39:13  Show Profile  Reply with Quote
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
Go to Top of Page

rsaver
Starting Member

USA
4 Posts

Posted - 02/25/2013 :  12:19:43  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000