Null result returned even though IS NOT NULL specified.

Hi guys,

I've got a query on a particular table returning an odd result:

SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStation

This query returns the rows I'd expect plus a null row. This doesn't happen in databases at other sites, or in other tables at this site. The following query behaves as I'd expect returning only non-null AccountNumbers.

SELECT DISTINCT AccountNumber
FROM Suppliers
WHERE AccountNumber Is Not Null
ORDER BY AccountNumber

I can't reproduce these results on another site on a table of the same structure, or on another table at this site.

Any suggestions as to what might be going on?

Pertinent info:
--
select @.@.Version

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
dbcc checkdb
Abridged result:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'POS'.
--
SELECT * INTO #Inv FROM Invoice

SELECT DISTINCT WorkStation
FROM #Inv
WHERE WorkStation Is Not Null
ORDER BY WorkStation

Does not reproduce this problem (and so is a probable fix) but the questions remains, what causes this?

TIA,
Karl.are you sure it's null, and not perhaps an empty string instead? try this --SELECT sum(case when WorkStation is null
then 1 end) as nulls
, sum(case when WorkStation = ''
then 1 end) as empties
, sum(case when WorkStation > ''
then 1 end) as somethings
, count(*) as total_rows
FROM Invoice and let's see what kind of totals you get|||Hi Rudy,

Thanks for your reply

are you sure it's null, and not perhaps an empty string instead? try this --SELECT sum(case when WorkStation is null
then 1 end) as nulls
, sum(case when WorkStation = ''
then 1 end) as empties
, sum(case when WorkStation > ''
then 1 end) as somethings
, count(*) as total_rows
FROM Invoice and let's see what kind of totals you get

Returns:
nulls empties somethings total_rows
---- ---- ---- ----
25212 NULL 2660565 2685777

(1 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

It would seem that the nulls are slipping into somewhere they shouldn't be for you too :)|||It would seem that the nulls are slipping into somewhere they shouldn't be for you too :)no, what makes you say that?

25212 + 0 + 2660565 = 2685777

so everything is accounted for

you said earlier that this query returns a NULL row --SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStationmay i ask how do you know it does this? where are you running the query, and how do you detect the NULL?|||no, what makes you say that?

An incorrect assumption on my part. Moving right along :)

you said earlier that this query returns a NULL row --SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStationmay i ask how do you know it does this? where are you running the query, and how do you detect the NULL?

This error was reported by the client who uses this database (the app they use uses ADO) but I've been using Query Analyzer to see it.

My method of null detection and confirmation is twofold:
- Occular examination (it's the first record and there's only ~20 rows)
- SELECT WorkStation
FROM Invoice
WHERE WorkStation = 'NULL' returns no rows.

-Karl.|||of course WHERE WorkStation = 'NULL' returns 0 rows

the correct syntax is WHERE WorkStation IS NULL|||Thanks for your time Rudy,

of course WHERE WorkStation = 'NULL' returns 0 rows

the correct syntax is WHERE WorkStation IS NULL

My intention for that second query is to show the 'NULL' result from the first query was in fact a null, and not a string containing the phrase 'NULL' as the two appear identical in the result pane.|||I know this has been somewhat covered, but just for gits and shiggles, what happens if you do this?

SELECT DISTINCT WorkStation
FROM Invoice
WHERE IsNull(WorkStation, '') <> ''
ORDER BY WorkStation|||Thanks for your reply Teddy,

That query returns the results I hoped that I'd get from the original one. It returns a list of workstations and contains no blank, or null, records.

I think I'm just going to:
-select the data into a temp table
-drop the original table
-select the data the original table
as I believe this will cease the behaviour, but it does mean we never find out why it occurred...|||If the query I posted returns your intended results, I would highly suggest you make sure you don't have empty strings in your workstation column. What that clause does is first convert null values to an empty string, and then filter by anything that doesn't have an empty string. I would be curious if you had some fields with just spaces in them as well, as there is an environmental setting that would automagically trim those spaces to ''.

Example:

-- Create a scratch table
CREATE TABLE #NullVsEmpty (test_column char(1))

-- Dump two rows into scratch table. One is empty, one is null
INSERT INTO #NullVsEmpty VALUES ('')
INSERT INTO #NullVsEmpty VALUES (NULL)

-- Display whats in scratch table for reference
select * from #NullVsEmpty

-- Count where test_column is not null
SELECT COUNT(*) FROM #NullVsEmpty WHERE test_column IS NOT NULL

-- Count where test_column is not EQUAL TO an empty string.
-- Note this returns 0 because Null cannot be directly compared to a scalar value
SELECT COUNT(*) FROM #NullVsEmpty WHERE test_column <> ''

-- Convert test_column to a finite value and then do comparison
SELECT COUNT(*) FROM #NullVsEmpty WHERE ISNULL(test_column, '') <> ''

DROP TABLE #NullVsEmpty