is null not working correctly - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

is null not working correctly

Author

Topic

csphard
Posting Yak Master

113 Posts

Posted - 2009-06-26 : 19:51:46

I am performing the following statement and see that some of the is null actually have a value. Is there anything that I am missing when it comes to checking a field for null values.

SELECT d.due_evals_id
FROM emp_information e,due_evals d
where e.empid = d.empid
and e.deptno = 999
AND d.rating IS NULL

Howard

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-26 : 20:20:22

There is nothing wrong with the query but how can you see records with NULL in d.rating is being selected ?

KH
[spoiler]Time is always against us[/spoiler]

csphard
Posting Yak Master

113 Posts

Posted - 2009-06-26 : 20:38:21

I am trying to do the following but when I run
it I see that I am hitting items that do have rating
I just do not see why.

update due_evals_bk_test
set payloc_comp_date = '06/26/2009',
comments = 'IC test'

where due_evals_id in (select d.due_evals_id
from emp_information_test e,due_evals d
where d.empid = e.empid
and e.deptno = 999
and d.rating is null
and payloc_comp_date is null)

howard

csphard
Posting Yak Master

113 Posts

Posted - 2009-06-26 : 20:54:40

The following worked but I do not understand why? Can anyone see what I missed
UPDATE due_evals_bk_test
SET payloc_comp_date = '06/26/2009',
comments = 'IC F test'
WHERE due_evals_id IN
(SELECT d.due_evals_id
FROM emp_information_test e ,
due_evals_bk_test d
where e.empid = d.empid
and e.deptno = 999
AND d.rating IS NULL AND d.payloc_comp_date IS NULL)

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-26 : 21:10:41

will is possible = '' instead of IS NULL?

csphard
Posting Yak Master

113 Posts

Posted - 2009-06-26 : 21:30:12

I have to environments. I looked at the create table statement
The one that is not working currectly has this statement
SET ANSI_NULLS ON
the other does not. I am looking at this but do not understand why they yield different results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-27 : 01:42:17

quote:
Originally posted by csphard

I have to environments. I looked at the create table statement
The one that is not working currectly has this statement
SET ANSI_NULLS ON
the other does not. I am looking at this but do not understand why they yield different results.

i dont think IS NULL will be affected by ANSI NULLS setting. ANSI NULL if set then it folows ansi setting where NULL wiil not be regarded as a value and =,>,<... wont work with NULL values whereeas when set to OFF NULL will be regarded as a value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources