WHERE condition with NULL not working consistently

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)

WHERE condition with NULL not working consistently

Author

Topic

tredekka
Starting Member

2 Posts

Posted - 2007-09-10 : 17:12:37

Good Afternoon,

I've a perplexing issue that has come up recently. The following two queries do not return the same results:

SELECT * FROM ltblLocationSales WHERE ((dtTermination IS NULL) OR (dtTermination >= GETDATE()) AND (dtShowOnDSR IS NULL) OR (dtShowOnDSR >= GETDATE()))

SELECT * FROM ltblLocationSales WHERE ((dtShowOnDSR IS NULL) OR (dtShowOnDSR >= GETDATE()) AND (dtTermination IS NULL) OR (dtTermination >= GETDATE()))

The first query returns 6 results incorrectly (in my opinion.) The second query returns 5 results.

The five records returned that both agree upon have NULL for both dtTermination and dtShowOnDSR. The differing record contains the following values:
dtTermination = NULL
dtShowOnDSR = 2000-01-01 00:00:00.000

Both queries exclude properly the records that have a date for both dtTermination and dtShowOnDSR.

Further testing has shown, that if in the record in question I change the values to:
dtShowOnDSR = NULL
dtTermination = 2000-01-01 00:00:00.000
Then the queries in question return 5 & 6 results.?.

Therefore it seems the WHERE clause is not working correctly, but I'm not sure why?

Any help would be greatly appreciated, thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 17:19:02

Do you have an issue with the time part in either dtShowOnDSR or dtTermination columns?

E 12°55'05.25"
N 56°04'39.16"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-10 : 17:21:55

not really efficient...

where coalesce(dttermination,getdate())>=getdate()
and coalesce(dtshowondsr,getdate())>=getdate()

or group together

where (dttermination>=getdate() or dttermination is null)
and (dtshowondsr>=getdate() or dtshowondsr is null)

you just need to know what you really want to get

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 17:23:46

I think you have an issue with missing paranthesises.
Learn operator presedence.

These are your queries

SELECT *
FROM ltblLocationSales
WHERE	(
		(dtTermination IS NULL)
		OR (dtTermination >= GETDATE() ) AND (dtShowOnDSR IS NULL)
		OR (dtShowOnDSR >= GETDATE() ) 
	)


SELECT *
FROM	ltblLocationSales
WHERE	(
		(dtShowOnDSR IS NULL)
		OR (dtShowOnDSR >= GETDATE()) AND (dtTermination IS NULL)
		OR (dtTermination >= GETDATE())
	)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 17:26:07

What I think you mean you want is this

SELECT	*
FROM	ltblLocationSales
WHERE	(
		dtTermination IS NULL OR dtTermination >= GETDATE()
	)
	AND 
	(
		dtShowOnDSR IS NULL OR dtShowOnDSR >= GETDATE()
	)


SELECT	*
FROM	ltblLocationSales
WHERE	(
		dtShowOnDSR IS NULL OR dtShowOnDSR >= GETDATE()
	)
	AND
	(
		dtTermination IS NULL OR dtTermination >= GETDATE()
	)

E 12°55'05.25"
N 56°04'39.16"

tredekka
Starting Member

2 Posts

Posted - 2007-09-10 : 17:45:40

Thank you very much for all of your quick answers, I appreciate the help.

In summary, the answer was a missing set of parenthesis, near the AND...:

Incorrect:

SELECT * FROM ltblLocationSales WHERE ((dtTermination IS NULL) OR (dtTermination >= GETDATE()) AND (dtShowOnDSR IS NULL) OR (dtShowOnDSR >= GETDATE()))

Correct:

SELECT * FROM ltblLocationSales WHERE ((dtTermination IS NULL) OR (dtTermination >= GETDATE()) ) AND ( (dtShowOnDSR IS NULL) OR (dtShowOnDSR >= GETDATE()))

Thanks again!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 18:41:02

Here is a perfect example of using CURRENT_TIMESTAMP function in favor of GETDATE().
It is much easier to keep track of paranthesises.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources