OR condition cancels AND condition

Question

Post reply

OR condition cancels AND condition

davidhohf

SSC Enthusiast

Points: 114
More actions
March 13, 2015 at 7:29 am

#295947

Hi All,
I'm hoping for some help in my WHERE statement below. I'm trying to pull data from where the date must be from yesterday but it also must complete a few or statements. But whenever I add in the OR statements SQL then completely ignores my date condition. I've tried so many different combinations including adjusting my order of operations with many brackets but i'm not getting anywhere.
I'm pretty sure its some type of order of the ANDs\ORs but I just can't figure it out.
here's my statement that fails:
WHERE
b.field1 LIKE 'ABC'
AND b.field2 BETWEEN GETDATE() - 30 AND GETDATE()
OR c.field3 != 'A'
OR a.field4 = '0'
OR a.field5 NOT IN ('1', '2', '7', 'B')
OR d.field6 < 2
OR d.field7 NOT IN ('CO','LT','B7')
Please help i'm just about ready to bang my head against the wall!!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

do any of those fields have nulls in them?

i think the "not in" portions can doink things up, right?

OR {NULL} NOT IN ('1', '2', '7', 'B')

...

OR {NULL}NOT IN ('CO','LT','B7')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

davidhohf SSC Enthusiast Points: 114 More actions · Answer 2

Hi Lowell,

Thanks for responding so fast!!

Yea the data in field3 does have NULL values (in fact most of them are NULL).

The rest of the fields do not have any null values.

Lowell SSC Guru Points: 323518 More actions · Answer 3

my knee jerk reaction would be to handle nulls for inequalities and exists, like this...

does this get expected data with this change?

WHERE

b.field1 LIKE 'ABC'

AND b.field2 BETWEEN GETDATE() - 30 AND GETDATE()

OR ISNULL(c.field3,'') != 'A'

OR a.field4 = '0'

OR ISNULL(a.field5,'') NOT IN ('1', '2', '7', 'B')

OR ISNULL(d.field6,0) < 2

OR ISNULL(d.field7,'') NOT IN ('CO','LT','B7')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

The main thing that immediately comes to mind is that you're missing brackets.

AND takes precedence over OR, meaning without brackets, your WHERE clause actually means

(b.field1 LIKE 'ABC' AND b.field2 BETWEEN GETDATE() - 30 AND GETDATE())

OR c.field3 != 'A'

OR a.field4 = '0'

OR a.field5 NOT IN ('1', '2', '7', 'B')

OR d.field6 < 2

OR d.field7 NOT IN ('CO','LT','B7')

If that's not what you intended, then you need to put brackets around the ORs that belong together.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 5

also

b.field1 LIKE 'ABC'

is exactly the same as

b.field1 = 'ABC'

maybe you meant

b.field1 LIKE 'ABC%' --starts with

or

b.field1 LIKE '%ABC%' --contains

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

davidhohf SSC Enthusiast Points: 114 More actions · Answer 6

GilaMonster (3/13/2015)
The main thing that immediately comes to mind is that you're missing brackets.
AND takes precedence over OR, meaning without brackets, your WHERE clause actually means
(b.field1 LIKE 'ABC' AND b.field2 BETWEEN GETDATE() - 30 AND GETDATE())
OR c.field3 != 'A'
OR a.field4 = '0'
OR a.field5 NOT IN ('1', '2', '7', 'B')
OR d.field6 < 2
OR d.field7 NOT IN ('CO','LT','B7')
If that's not what you intended, then you need to put brackets around the ORs that belong together.

Hi GilaMonster,

I tried added in brackets where I believe I needed them to separate the operations that I want and the max rows I should see is 1823 (I exluded the OR conditions and ran only the top 2) but when I added in backets i got 5.5Million+ rows before I cancelled the query (ran for 1m 33s)

Here's my where statement now:

WHERE

(b.field1 BETWEEN GETDATE() - 30 AND GETDATE() AND b.field2 LIKE '%148')

OR c.field3 != 'A'

OR a.field4 = '0'

OR a.field5 NOT IN ('1', '2', '7', 'B')

OR d.field6 < 2

OR d.field7 ('CO','LT','DF','BK','B7')

Also, Lowell I tried adding in ISNULL but did not populate the data that I expected.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

To expand on Gail's recommendation of brackets I am pretty sure you want something more like this.

WHERE

b.field1 LIKE 'ABC'

AND b.field2 BETWEEN GETDATE() - 30 AND GETDATE()

AND

(

c.field3 != 'A'

OR a.field4 = '0'

OR a.field5 NOT IN ('1', '2', '7', 'B')

OR d.field6 < 2

OR d.field7 NOT IN ('CO','LT','B7')

)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

dlsql SSC Enthusiast Points: 186 More actions · Answer 8

Yeah think Seans got what you need there.

Just to explain why previously it ignored the date condition

your initial statement says return everything where

b.field2 BETWEEN GETDATE() - 30 AND GETDATE()

OR ---> other conditions this means that any of the following conditions can be true and it will be returned regardless of the date condition.

Seans code says that for the result to be returned the date condition must be met AND one of the following OR statements must be met

😀

davidhohf SSC Enthusiast Points: 114 More actions · Answer 9

Hi All,

Thanks for all the help. Yes it does apear that Sean's suggestion does work. We're still auditing the data but over a first review it appears accurate as intended!!