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.
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 RetireePremiumsGroup by PremiumGroupOrder by PremiumGroup
The results:0 NULL27 A94 B124 C32 D345 E193 F16 G195 New LA7 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.
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 intDECLARE @val2 int
If (isnull(@val1,null) = isnull(@val2,null)) print 'True'else print 'False'
SET @val1 = NULLSET @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??..
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.
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