Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Question Unanswered: How to avoid returning NULL fields

    Hello,

    There is a table with a field called "ContractType" I want to return
    all rows which are not equal to 'Customer buy'. There are records
    which have NULL values in this field, and I want to return those records:

    Select * from myTable
    Where ContractType <> 'Customer buy'

    But this query doesn't return the records with ContractType value of NULL.

    Any ideas?

    Thank you in advance...

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's right it won't...

    Select * from myTable
    Where ISNULL(ContractType,'x') <> 'Customer buy'

    Should do it...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or:

    Select * from myTable
    Where ContractType <> 'Customer buy' or ContractType is null

    I wonder if the two have the same query plan...

    blindman

  4. #4
    Join Date
    Oct 2003
    Posts
    3
    Brett,

    It did work, Thanks! It seems that you have to evaluate
    NULL to some value before comparing it to something else.

    Appreciate your help on this...

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett's should have worked. Are you sure you are dealing with NULLS and not zero-length strings? A zero-length string is not null, but is actually a string with no characters (sounds like we're entering the quantum world...).

    Try this:

    Where ContractType <> 'Customer buy' or nullif(ContractType, '') is null

    blindman

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by liberator28
    Brett,

    It did work, Thanks! It seems that you have to evaluate
    NULL to some value before comparing it to something else.

    Appreciate your help on this...
    Um blindman?

    You must forgive hime liberator28 , because...well because he's....umm....blind...

    ;-)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I probably should have read my employment contract more carefully too...

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    I probably should have read my employment contract more carefully too...
    LOL
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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