Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Posts
    29

    Unanswered: Null result returned even though IS NOT NULL specified.

    Hi guys,

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

    Code:
    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.

    Code:
    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:
    ---
    Code:
    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)
    ---
    Code:
    dbcc checkdb
    Abridged result:
    CHECKDB found 0 allocation errors and 0 consistency errors in database 'POS'.
    ---
    Code:
    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.
    Last edited by karlmdv; 04-25-07 at 21:43.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are you sure it's null, and not perhaps an empty string instead? try this --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    29
    Hi Rudy,

    Thanks for your reply

    Quote Originally Posted by r937
    are you sure it's null, and not perhaps an empty string instead? try this --
    Code:
    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:
    Code:
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by karlmdv
    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 --
    Code:
    SELECT DISTINCT WorkStation 
    FROM Invoice 
    WHERE WorkStation Is Not Null 
    ORDER BY WorkStation
    may i ask how do you know it does this? where are you running the query, and how do you detect the NULL?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2005
    Posts
    29
    Quote Originally Posted by r937
    no, what makes you say that?
    An incorrect assumption on my part. Moving right along


    Quote Originally Posted by r937
    you said earlier that this query returns a NULL row --
    Code:
    SELECT DISTINCT WorkStation 
    FROM Invoice 
    WHERE WorkStation Is Not Null 
    ORDER BY WorkStation
    may 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)
    -
    Code:
    SELECT WorkStation 
    FROM Invoice 
    WHERE WorkStation = 'NULL'
    returns no rows.

    -Karl.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    of course WHERE WorkStation = 'NULL' returns 0 rows

    the correct syntax is WHERE WorkStation IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2005
    Posts
    29
    Thanks for your time Rudy,

    Quote Originally Posted by r937
    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.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I know this has been somewhat covered, but just for gits and shiggles, what happens if you do this?

    Code:
    SELECT DISTINCT WorkStation 
    FROM Invoice 
    WHERE IsNull(WorkStation, '') <> ''
    ORDER BY WorkStation
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Jan 2005
    Posts
    29
    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....

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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:

    Code:
    -- 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
    Last edited by Teddy; 05-07-07 at 14:40.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •