is this something wrong with this query

Question

is this something wrong with this query

Vishal Singh

SSCertifiable

Points: 5830
More actions
August 4, 2011 at 4:41 am

#390123

Hi Pals,
Not sure wwhatswrong with this query but whenever I am running this, I am getting all those rows with State='closed' which it should ignoring.
SELECT [ID],Priority,Title,[Created By],[Assigned To], [State],History,[CReated Date],[Closed Date], [Closed Date], Comments, Keywords
FROM dbo.t_BugReport WHERE State <> N'Closed'
AND Title LIKE N'%Bahamas%'
OR History LIKE N'%TheGreatCharlie%'
OR [Repro Steps] LIKE N'%Anything%'
not sure why it returning even those rows which ha state=closed..
-Forum Etiquette: How to post Performance Problems[/url]
-Forum Etiquette: How to post data/code to get the best help [/url]

Viewing 7 posts - 1 through 6 (of 6 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Vishal Singh (8/4/2011)
Hi Pals,
Not sure wwhatswrong with this query but whenever I am running this, I am getting all those rows with State='closed' which it should ignoring.
SELECT [ID],Priority,Title,[Created By],[Assigned To], [State],History,[CReated Date],[Closed Date], [Closed Date], Comments, Keywords
FROM dbo.t_BugReport WHERE State <> N'Closed'
AND Title LIKE N'%Bahamas%'
OR History LIKE N'%TheGreatCharlie%'
OR [Repro Steps] LIKE N'%Anything%'
not sure why it returning even those rows which ha state=closed..

Check the logic of your WHERE clause 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 2

Tried placing the State <> 'Closed' filter here and there but not helping much.

If I am removing one 'OR' filter its working but I need to make it work with all the filters mentioned in the query.

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Try these two variants of your WHERE clause:

WHERE ([State] <> N'Closed'

AND Title LIKE N'%Bahamas%')

OR History LIKE N'%TheGreatCharlie%'

OR [Repro Steps] LIKE N'%Anything%'

WHERE [State] <> N'Closed'

AND (Title LIKE N'%Bahamas%'

OR History LIKE N'%TheGreatCharlie%'

OR [Repro Steps] LIKE N'%Anything%')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

Vishal Singh (8/4/2011)
Hi Pals,
Not sure wwhatswrong with this query but whenever I am running this, I am getting all those rows with State='closed' which it should ignoring.
SELECT [ID],Priority,Title,[Created By],[Assigned To], [State],History,[CReated Date],[Closed Date], [Closed Date], Comments, Keywords
FROM dbo.t_BugReport WHERE State <> N'Closed'
AND Title LIKE N'%Bahamas%'
OR History LIKE N'%TheGreatCharlie%'
OR [Repro Steps] LIKE N'%Anything%'
not sure why it returning even those rows which ha state=closed..

Brackets are your friend in this instance, look at the where clause and consider which part needs to be put into the brackets.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 5

http://msdn.microsoft.com/en-us/library/ms190276.aspx

http://msdn.microsoft.com/en-us/library/ms186992.aspx

the above two article explain how operators work. You need to group operators using brackets to make sure you get the required results.

e.g

GO

SELECT ProductID, ProductModelID

FROM AdventureWorks2008R2.Production.Product

WHERE ProductModelID = 20 OR ProductModelID = 21

AND Color = 'Red'

AND

SELECT ProductID, ProductModelID

FROM AdventureWorks2008R2.Production.Product

WHERE (ProductModelID = 20 OR ProductModelID = 21)

AND Color = 'Red'

Are two different queries, because in the first case the where condition is understood as follows:-

(Find all Red cars with modelid = 21 ) or All cars with model 20

the second where clause is understood as

Find all red cars where the model ID is (either 20 or 21)

Jayanth Kurup[/url]

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 6

Thanks Guys, I got the clue..:-)

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]