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.
| Author |
Topic |
|
barnabeck
Posting Yak Master
236 Posts |
 Posted - 2012-06-19 : 11:36:51
|
Am I missing something?I write the following expression in the where clauseCoE = 'UPG' AND ((YEAR(CREATEDDATETIME) = '2012' AND MONTH(CREATEDDATETIME) <= '5') OR (YEAR(CREATEDDATETIME) < '2012')) and it gets converted into(CoE = 'UPG') AND (YEAR(CREATEDDATETIME) = '2012') AND (MONTH(CREATEDDATETIME) <= '5') OR (CoE = 'UPG') AND (YEAR(CREATEDDATETIME) < '2012') Although in the execution of the query "my" idea of the logic chain is respected, the expression SQL Management Studio converts it into is not correct without those brackets that combine the two mayor OR-clauses. I learned that as a teen: (A AND B) OR (C AND D) IS NOT A AND B OR C AND DDoes SQL Management Studio has its own logic rules?Martin |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-06-19 : 11:47:08
|
| Couldn't you do this?CoE = 'UPG' and CREATEDDATETIME < '20120601'JimEveryday I learn something that somebody else already knew |
 |
|
|
barnabeck
Posting Yak Master
236 Posts |
Posted - 2012-06-19 : 11:54:56
|
| Thank you Jim,and off course I could do that... and I actually already did to avoid the "OR", but I keep wondering the same????? |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-06-19 : 12:07:44
|
| I believe that A and (B or C) is the same as (A and B) or (A and C), which is what the optimizer turned your logic into. I'd go with my qhere clause anyway, the optimizer will like it much more, especially if there's an index on createddatetime.JimEveryday I learn something that somebody else already knew |
|
|
|
barnabeck
Posting Yak Master
236 Posts |
Posted - 2012-06-19 : 12:22:50
|
| Yes, you are right A and (B or C) is (A and B) or (A and C) but what the optimizer did was turn it into A and B or A and C, without the brackets. This is what I'm still wondering about, because as I understood Mr. Boole, one could read A and B or A and C in exactly the same way as A and (B or A) and C, which is nonsense.Martin |
|
|
|
|
|
|
brackets in logical chains