| Author |
Topic |
|
meef
Posting Yak Master
113 Posts |
 Posted - 2011-09-07 : 14:51:46
|
I'm trying to use this WHERE clause:WHERE (shdt BETWEEN '2/1/11' AND '6/30/11') AND (acct = 'bur') AND (conm LIKE 'home%') OR (conm LIKE 'wal%') OR (conm LIKE 'lowe%') OR (conm LIKE 'targ%') OR (conm LIKE 'menard%') OR (conm LIKE 'kmar%') It will filter the date accordingly until I start adding the other conm fields there. Once I put the ORs in it just shows me every date instead of the range I try to specify. I know I've run into this before but forget how to fix it. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-09-07 : 15:02:06
|
| This is an operator precedence issue. The AND operation will be given higher precedence than the OR operators. You want to put parentheses around all of you OR terms.=======================================The first rule of Tautology Club is the first rule of Tautology Club. -xkcd, (Honor Societies) |
 |
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2011-09-07 : 15:16:53
|
| Not sure I understand where else to put the parenthesis, I already have them around each OR statement... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2011-09-07 : 16:17:51
|
quote:
Originally posted by tkizer
Better yet as I hate all of the unnecessary ones:(conm LIKE 'home%' ORconm LIKE 'wal%' ORconm LIKE 'lowe%' ORconm LIKE 'targ%' ORconm LIKE 'menard%' ORconm LIKE 'kmar%')Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
When I try that, the moronic program takes them away and just puts them around each line again like in my original post... or maybe I'm the moron, who knows, but it's not letting me do that.EDIT: Apparently you need to make a new query to make it leave your syntax alone.That didn't work either though, still getting dates from 2009.WHERE (shdt BETWEEN '2/1/11' AND '6/30/11') AND (acct = 'bur') AND (conm LIKE 'home%') OR (conm LIKE 'home%' OR conm LIKE 'wal%' OR conm LIKE 'lowe%' OR conm LIKE 'targ%' OR conm LIKE 'menard%' OR conm LIKE 'kmar%') |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-09-07 : 16:29:57
|
hard to say since you haven't given us any information with than your where clause. Maybe this? WHERE (shdt BETWEEN '2/1/11' AND '6/30/11') AND (acct = 'bur') AND ( (conm LIKE 'home%') OR (conm LIKE 'wal%') OR (conm LIKE 'lowe%') OR (conm LIKE 'targ%') OR (conm LIKE 'menard%') OR (conm LIKE 'kmar%') ) If that doesn't work please post DDL, DML and Expected Output. This link can help you put that together:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2011-09-07 : 16:33:04
|
quote:
Originally posted by tkizer
What program are you using?You've got conm LIKE 'home%' outside of the parenthesis.WHERE (shdt BETWEEN '2/1/11' AND '6/30/11' AND acct = 'bur') AND (conm LIKE 'home%' OR conm LIKE 'wal%' OR conm LIKE 'lowe%' OR conm LIKE 'targ%' OR conm LIKE 'menard%' OR conm LIKE 'kmar%')Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Whoops, looks like that was it and I just missed the other "conm" there. Eyes played tricks on me.Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
WHERE clause doesn't filter on date