Post reply

Field with NULL value not included in result

  • February 11, 2004 at 8:01 am

    #107533

    I have written a script that will exclude from the result set a particular value in field. What is also being excluded from the result set are those rows that contain null values in the field being tested. If I remove the condition all rows are included.

    Any help would greatly be appreciated

  • February 11, 2004 at 8:06 am

    #493651

    Check "set ansi_nulls" setting.

  • February 11, 2004 at 8:53 am

    #493663

    First you must understand that NULL is an unknown value so when you conditionally exclued data the value cannot be determined and could in fact be the value to exclude.

    If you reverse and decide to show only those where the condition is meet the same thing happens NULLS are unknown and cannot be processed as part of the set.

    Awalys check a nullable column to ensure explicit handlin of a condition occurrs. To include in results do

    ColumnName IS NOT NULL

    to exclude

    ColumnName IS NULL

    This insures the compiler knows which way to evaluate the unknown.

  • February 12, 2004 at 1:51 am

    #493757

    If I understand ur question I think ur looking for something like this?

    Select * from orders where shipregion is not null




    My Blog: http://dineshasanka.spaces.live.com/

  • February 12, 2004 at 6:32 am

    #493788

    You need to use the following generic format if, as in this example, col3 allows null values:

    SELECT col1, col2, col3

    FROM myTable

    WHERE col3 <> 'X' AND col3 IS NOT NULL

    Mike

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply