The Borland Delphi application I administer uses a SQL Server 2000 back end with ANSI NULLS set to ON. If I remove a date from a field in the application, it doesnt return to NULL, it returns to a non-NULL value which is not displayed, so I have to explicitly set the value to NULL if the user removes the date otherwise my IS NULL comparisons will not work

David le Quesne

I agree with axeld1980.

The statements made about setting a value to null rather than just not setting it are incorrect. It makes absolutely no difference whether the value is assigned to null or not.

The results you get are wholly dependent on the value for ANSI_NULLS. With this set to ON, nothing will ever '= NULL', not even if it is set explicitly. With the setting OFF, both '= NULL' and 'IS NULL' behave in the same way. So:

WHY CAN'T NULL = NULL?????????????

That has always been my biggest problem in dealing with Nulls. Null does equal Null in the real world. If Null = the absence of a value, and you have two instances of the absence of a value, they are the same, Null.

If the field value is Null, the absence of a value in that field, IT EQUALS NULL!!!

I just don't get why it has to be so complicated. Humans write the software and we can make it evaluate Nulls any way we want to, so why can't it just be simple, Null = Null, "Is Null" should be the same thing as "= Null", but not the same thing as "= 'Null'".

"Select Count(Field1) Where Field1 Is Null" should return the number of records that have a Null value for that field, regardless ofthe ANSI Nulls setting.

I think we could all get along just fine knowing that Null is a special value (or lack of) that is different than 'Null' (string of letters) but Null = Null.

Chris

WHY CAN'T NULL = NULL?????????????

That is why any test for '= NULL' will fail.

Also, re your comment about using IS NULL,
"Select Count(Field1) Where Field1 Is Null" DOES return the number of records that have a Null value for that field, regardless ofthe ANSI Nulls setting.

I just ran into this yesterday, and could not get a count for Null values if I was using the field with the Nulls in it for the count(), regardless of the ANSI Nulls setting. If I used a different field, such as the ID field, in the count(), I did get the count correctly.

The query:
Select Count(PremiumGroup), PremiumGroup From RetireePremiums
Group by PremiumGroup
Order by PremiumGroup

The results:
0 NULL
27 A
94 B
124 C
32 D
345 E
193 F
16 G
195 New LA
7 Special

There are actually over 2000 records with a Null in that field.

But in any case, Null can be undetermined value, value not assigned, absence of value, etc. but in reality it is the same thing, no value, and should be recognized as its own special value to make logic easier when programming.

I am unable to imagine how making Nulls easier to deal with could make life more difficult for any of us, or our logic.

Thanks,
Chris

hi!!, i was playing around with my QA and tryin' to find the perfect query to avoid this NULL issues. Of course, i still haven't found it.

I think this is like... try to explain what means nothing (in Real Life ).

SET ANSI_NULLS [Off | on]

DECLARE @val1 int
DECLARE @val2 int

If (isnull(@val1,null) = isnull(@val2,null))
    print 'True'
else
    print 'False'

SET @val1 = NULL
SET @val2 = NULL
 
If (isnull(@val1,null) = isnull(@val2,null))
    print 'True'
else
    print 'False'

Well i know this could not happen in real cases, but is curious how the result of this is always False, why??, i'm not expert in SQL Server but.. for SET ANSI_NULLS Off .. the result shouldn't be TRUE???

can anybody explain this??.. 

thanks!!

Familiarize yourself with the SQL function isnull().

When comparing a field which may have a null value, instead of = CustomerID

use = isnull(CustomerID, '')

which means: If the customerid is null, treat it as '', an empty string.

Good Luck

FYI sql like 'customerid = ' & sCustomerID

Can be 'isnull(customerid) = isnull(' & sCustomerID & ')'

or it can be '(customerid is null and ' & sCustomerID & ' is null) or customerid = ' & sCustomerID