|
Posted by Plamen Ratchev on 02/06/07 18:11
Hi Paul,
Here is one more option to check. I have seen similar behavior on client
databases when somebody turned the CONCAT_NULL_YIELDS_NULL option to OFF.
Basically when it is OFF a string concatenation like SELECT TAB1.DESCRIPTION
+ NULL will return the string in TAB1.DESCRIPTION, not NULL. When
CONCAT_NULL_YIELDS_NULL is ON then SELECT TAB1.DESCRIPTION + NULL will
return NULL. I am not sure what is the logic and SQL of your application but
if you have string concatenation it could be it.
A good example of how it will affect returned records is if you have a query
like this:
SELECT FirstName + ' ' + MiddleInitial + ' ' + LastName AS EmployeeName
FROM Employees
WHERE FirstName + ' ' + MiddleInitial + ' ' + LastName IS NOT NULL
Then if you have MiddleInitial set to NULL for some records you will see
different results based on CONCAT_NULL_YIELDS_NULL being ON or OFF.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Navigation:
[Reply to this message]
|