开发者

querying using != against a field that has some NULL values

I have report that references a view in a SQL Server. The user can filter on a char field, but when they filter using "!=" to a value it filters out those records with that value AND any records that have a NULL value in that field. I tested this outside of the report, by putting the filter in the WHERE clause of SQL code and got the same results. Why is this happening? How can I get around this issue?

For instance:

SELECT f1, f2, f3
FROM aTable

 f1   f2   f3
---- ---- ----
 Y    Y    NULL
 Y    N    M
 N    Y    N
 Y    N    NULL

SELECT f1, f2, f3
FROM aTable
WHERE f3 != 'N'

 f1   f2   f3
---- ---- ----
 Y    N    M

开发者_开发技巧Please let me know if you need additional information.

Any insight you can provide would be helpful :-)

Thanks,

Ben


Comparison operations with NULL always evaluate to false. You can use:

WHERE f3 is null or f3 != 'N'


You can use ISNULL function

SELECT f1, f2, f3 FROM aTable WHERE ISNULL(f3,'')!='N'
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜