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

NULL output when trying to script objects with SQL-DMO

The following statement SHOULD generate a script of the specified stored
procedure. Instead, it returns NULL. Please tell me what I'm doign wrong:
DECLARE @.oServer int
DECLARE @.method varchar(300)
DECLARE @.TSQL varchar(4000)
DECLARE @.ScriptType int
EXEC sp_OACreate 'SQLDMO.SQLServer', @.oServer OUT
EXEC sp_OASetProperty @.oServer, 'loginsecure', 'true'
EXEC sp_OAMethod @.oServer, 'Connect', NULL, 'US1SQLDEV'
SET @.ScriptType = 1|4|32|64|256|262144
SET @.method = 'Databases("Compass").' +
'StoredProcedures("p_rpt_Distance").Script' +
'(' + CAST (@.ScriptType AS VARCHAR) + ')'
EXEC sp_OAMethod @.oServer, @.method ,
@.TSQL OUTPUT
SELECT @.TSQL
EXEC sp_OADestroy @.oServercongratulations, you've found the slowest possible way to do this ;)
if your sql is going to be <=4000 chars, you should just go against
syscomments. otherwise, your variable won't be big enough, anyway.
if you're dead-set on using the object junk, check the script type.
you've got the script type of 64 (To File only) set, but no file name to
output to.
CadeBryant wrote:
> The following statement SHOULD generate a script of the specified stored
> procedure. Instead, it returns NULL. Please tell me what I'm doign wrong
:
> DECLARE @.oServer int
> DECLARE @.method varchar(300)
> DECLARE @.TSQL varchar(4000)
> DECLARE @.ScriptType int
> EXEC sp_OACreate 'SQLDMO.SQLServer', @.oServer OUT
> EXEC sp_OASetProperty @.oServer, 'loginsecure', 'true'
> EXEC sp_OAMethod @.oServer, 'Connect', NULL, 'US1SQLDEV'
> SET @.ScriptType = 1|4|32|64|256|262144
> SET @.method = 'Databases("Compass").' +
> 'StoredProcedures("p_rpt_Distance").Script' +
> '(' + CAST (@.ScriptType AS VARCHAR) + ')'
> EXEC sp_OAMethod @.oServer, @.method ,
> @.TSQL OUTPUT
> SELECT @.TSQL
> EXEC sp_OADestroy @.oServer