Author |
Topic |
M1North
Starting Member
9 Posts |
Posted - 2011-11-18 : 15:46:42
|
HelloI am querying a view using the below:SELECT FileTitle, DeletedBy, ActiveFromDate, ActiveToDateFROM SomeFilesWHERE (DeletedBy IS NOT NULL) OR ((ActiveFromDate >= GETDATE()) AND (ActiveToDate <= GETDATE()))The results returned include columns where DeletedBy is null (second active date criteria is false)... If I change it to (DeletedBy != null), the null records are excluded.Strangely, if I do something like:SELECT FileTitle, DeletedBy, ActiveFromDate, ActiveToDate, LEN(DeletedBy)FROM SomeFilesWHERE (DeletedBy IS NOT NULL) OR ((ActiveFromDate >= GETDATE()) AND (ActiveToDate <= GETDATE()))The null records are also excluded.Please could somebody explain what is going on here.Thanks in advance. |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-11-18 : 15:50:15
|
I can go into more detail, but your logic is using an OR predicate. So, a row only needs to meet one of the two criterion in your WHERE clause.EDIT: ahh too slow.. :( |
|
|
M1North
Starting Member
9 Posts |
Posted - 2011-11-18 : 16:01:26
|
HiThe second active date criteria equates to false - the problem is pretty much to do with the DeletedBy clause because the same query using DeletedBy != null, correctly removes the null records...Cheers |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-11-18 : 16:44:50
|
I don't see a difference between where clause in the two queries you posted. Also, using "NOT NULL" versus "!= NULL" can have vastly different means, depending on your ANSI_NULLS settings. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-11-18 : 16:49:01
|
Maybe this will help illustrate. Try changing the ANSI_NULLS setting from ON to OFF:SET ANSI_NULLS ON -- SQL SERVER Defaul ON--SET ANSI_NULLS OFFDECLARE @Foo TABLE (ID INT, Val INT)INSERT @Foo (ID, Val)VALUES(1, NULL),(2, 20),(3, NULL),(4, 40),(5, 50),(6, NULL)SELECT *FROM @FooWHERE Val IS NOT NULLOR ID BETWEEN 3 AND 6SELECT *FROM @FooWHERE Val <> NULLOR ID BETWEEN 3 AND 6 |
|
|
M1North
Starting Member
9 Posts |
Posted - 2011-11-18 : 17:10:49
|
HiI'm probably not making myself clear... I have the following data:FileTitle | DeletedBy | ActiveFromDate | ActiveToDateTest | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000The following query returns null records (incorrectly):SELECTFileTitle, DeletedBy, ActiveFromDate, ActiveToDateFROMSomeFilesWHERE(DeletedBy IS NOT NULL)OR((ActiveFromDate >= GETDATE()) AND (ActiveToDate <= GETDATE()))BUT, the following do not:(Note, all that is different in the below query to the above is the addition of the LEN calculation)SELECTFileTitle, DeletedBy, ActiveFromDate, ActiveToDate, LEN(DeletedBy)FROMSomeFilesWHERE(DeletedBy IS NOT NULL)OR((ActiveFromDate >= GETDATE()) AND (ActiveToDate <= GETDATE()))And also,SELECTFileTitle, DeletedBy, ActiveFromDate, ActiveToDateFROMSomeFilesWHERE(DeletedBy != NULL)OR((ActiveFromDate >= GETDATE()) AND (ActiveToDate <= GETDATE()))Cheers |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-11-18 : 17:21:47
|
Humm, maybe you can show us yor result. Did you switch the greater than/less than around? I don't get any results for any of those queries and the given sample data (nor did I expect to). |
|
|
M1North
Starting Member
9 Posts |
Posted - 2011-11-18 : 17:29:01
|
It gets even more strange... I am only getting this behaviour on my database. If I export the data on to another database, I don't experience the issue.Could this be a DB specific setting?I'm getting all records returned from the 1st query |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-11-18 : 17:38:16
|
Odd. Is it possible that NULL got put in as a string and not an actual value? |
|
|
M1North
Starting Member
9 Posts |
Posted - 2011-11-18 : 17:42:34
|
No I've checked that, plus I get the colour differentiation in SQL2008 for nulls... |
|
|
M1North
Starting Member
9 Posts |
Posted - 2011-11-19 : 06:32:35
|
Hi LampreyI could e-mail you a copy of the database where you'll be able to replicate this - if you have the time...Cheers |
|
|
M1North
Starting Member
9 Posts |
Posted - 2011-11-19 : 06:33:30
|
quote: Originally posted by Lamprey Odd. Is it possible that NULL got put in as a string and not an actual value?
Hi LampreyI could e-mail you a copy of the database where you'll be able to replicate this - if you have the time...Cheers |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-11-19 : 12:20:06
|
!= NULL isn't a valid test of anything.Everything is != NULL. Even NULL != NULL.And...this testActiveFromDate >= GETDATE() AND ActiveToDate <= GETDATE()is unlikely to match anything either.I'm betting Lamprey is right that the sting value 'NULL' is in there. |
|
|
M1North
Starting Member
9 Posts |
Posted - 2011-11-20 : 04:42:50
|
quote: Originally posted by russell != NULL isn't a valid test of anything.Everything is != NULL. Even NULL != NULL.And...this testActiveFromDate >= GETDATE() AND ActiveToDate <= GETDATE()is unlikely to match anything either.I'm betting Lamprey is right that the sting value 'NULL' is in there.
RussellHave you even bothered to read the thread?I'm asking why the following query:SELECTFileTitle, DeletedBy, ActiveFromDate, ActiveToDateFROMSomeFilesWHERE(DeletedBy IS NOT NULL)OR((ActiveFromDate >= GETDATE()) AND (ActiveToDate <= GETDATE()))Is returning null records from:FileTitle | DeletedBy | ActiveFromDate | ActiveToDateTest | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000Test | NULL | 2011-11-18 00:00:00.000 | 2011-12-03 00:00:00.000And no, DeletedBy does not contain string 'NULL'. |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-11-20 : 09:08:48
|
How is the view 'SomeFiles' defined? Could be something in the view is causing this issue. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-11-21 : 00:47:16
|
Check youe ANSI_NULL setting on both yousr machine and the machine that works. N 56°04'39.26"E 12°55'05.63" |
|
|
M1North
Starting Member
9 Posts |
Posted - 2011-11-21 : 05:06:16
|
quote: Originally posted by jeffw8713 How is the view 'SomeFiles' defined? Could be something in the view is causing this issue.
HiThe view is defined as:SELECT dbo.SomeFiles.FileTitle, dbo.SomeFiles.ActiveFromDate, dbo.SomeFiles.ActiveToDate, dbo.aspnet_Users.UserName AS DeletedByFROM dbo.SomeFilesLEFT OUTER JOIN dbo.aspnet_UsersON dbo.Files.DeletedBy = dbo.aspnet_Users.UserIdCould it be the left join? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-11-21 : 16:28:35
|
Maybe you should try reading the answers. |
|
|
|