WHERE CLAUSE

Please find the attached code utilized in a WHERE clause for a Gridview.   The Gridview displays;  however,  it is not filtering properly.   The only filter that is working is the "station" the "time" range and "date" ranges are not functioning.
SQL-FILTER.docx
BOEING39Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

N-WCommented:
It's not functioning because you are using "&lt;" and "&gt;" in your SQL statements instead of "<" and ">".

Replace "&lt;" with "<" and "&gt;" with ">" in your SQL statement and it should work.

"&lt;" and "&gt;" is commonly used when writing HTML markup, but does not work for SQL queries.
0
käµfm³d 👽Commented:
"&lt;" and "&gt;" is commonly used when writing HTML markup
Ah, mon ami, but that is exactly what you are seeing:  ASP.NET markup.

I think this is a logic error. You have numerous AND & OR in your query. But you haven't grouped any of them together (via parentheses). This means you need to worry about precedence. If I recall correctly, AND binds tighter than OR, so anywhere that you have "something AND something-else OR another-something-else", you have effectively written:

(something AND something-else) OR another-something-else

Note the additional parens.

You need to employ a strategic placement of parentheses to force the query engine to use the precedence that you intended to occur.

P.S.

This is (effectively) what the query engine interprets your current query as:

[([Dates] &gt;= @Dates) AND ([Dates] &lt;= @Dates2) AND ([Dep] &gt;= @Dep) AND ([Dep] &lt;= @Dep2) AND ([Station] = @Station) AND (Code = 'MC')]

OR (Code = 'MM')
OR (Code = 'MA')
OR (Code = 'MF')
OR (Code = 'MH')
OR (Code = 'ML')
OR (Code = 'MN')

* Brackets removed from "Code" to satisfy the EE tag parser.
0
PortletPaulfreelancerCommented:
without changing the effect of your current select query, but reformatting it, it could appear like this
SELECT [ID], [Dates], [Ship], [Station], [Otbd], [Dep], [Code], [Time], [Code1], [Time1], [Wake], [Call], [MM], [RB], [Reason], [ATA], [RON], [GT], [Captain]
FROM [Delays]
WHERE (
		([Dates] >= @Dates)
		AND ([Dates] <= @Dates2)
		AND ([Dep] >= @Dep)
		AND ([Dep] <= @Dep2)
		AND ([Station] = @Station)
		AND ([Code] = 'MC')
		OR ([Code] = 'MM')
		OR ([Code] = 'MA')
		OR ([Code] = 'MF')
		OR ([Code] = 'MH')
		OR ([Code] = 'ML')
		OR ([Code] = 'MN')
		)
ORDER BY [Dates] DESC

Open in new window

I think this is an excellent opportunity to use IN () for that list of codes (it will help you avoid some of the precedence issues).

Additionally I would suggest that all the parentheses except those required for the IN () are currently redundant. That is, this would work equally as well without the current parentheses.
FROM [Delays]
WHERE [Dates] >= @Dates
        AND [Dates] <= @Dates2
        AND [Dep] >= @Dep
        AND [Dep] <= @Dep2
        AND [Station] = @Station
        AND [Code] IN ('MC', 'MM', 'MA', 'MF', 'MH', 'ML', 'MN')
ORDER BY
        [Dates] DESC

Open in new window

Q1. Now that we can see it a bit more clearly, now we need to ask ourselves, will all the parameters have values all the time?

Q2. Is the field type for [Dates] datetime2, datetime or smalldatetime, and do the records have values such as:
2013-09-07 23:44:55
i.e. do they store time other than 00:00:00

btw: you could just paste your raw code snippet into a comment - look for the word "code" in the toolbar - the script text should fit between the code tags.
NB: Word's curly quotes can nicely mess-up script :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
BTW,
  [Dates] >= @Dates  AND [Dates] <= @Dates2
is exactly the same as
   [Dates] between @Dates and @Dates2
which is more clear (same for Dep), so the reworked WHERE would look like this:
WHERE [Dates] between @Dates and @Dates2
  AND [Dep] between @Dep and @Dep2
  AND [Station] = @Station
  AND [Code] IN ('MC', 'MM', 'MA', 'MF', 'MH', 'ML', 'MN')

Open in new window

On an additional note, take care in regard of dates and "between"-like conditions - the boundaries are included, which often is not what you want to have, because in
/* Query 1 */ .... where dt between @date1 and @date2 ...
/* Query 2 */ .... where dt between @date2 and @date3 ...

Open in new window

you would get the same row matching @date2 in both queries.
0
PortletPaulfreelancerCommented:
Qlemo
the reason for my question Q1 and Q2 was to see if there is an inherent risk of the query being wrong :(

yes, you could use between - but I hate it for date ranges - and there is absolutely nothing inherently wrong with using >=  together with <=
although it may well need parentheses depending on answers to Q1 & Q2 IMHO

Beware of Between it's not always a good friend.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
But there is no function difference using >= AND <= or BETWEEN - en contraire, the former obfuscates the intention. between makes it more clear what you think you ask for (of course that might not be what you get).
0
PortletPaulfreelancerCommented:
we therefore disagree on the clarity
I believe >= with <=
is actually much more definitive (and clear)

there is a great deal of misunderstanding about the word between in the context of SQL simply because in common language it can have 3 basic meanings:

to fit between (> with <)
to fly between (>= with <=)
group of z between x and y (|>= with <| > with <=|)

clearly you know the SQL between is >= with <= and hence accommodate it: I'm not suggesting otherwise.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That pretty much covers everything ;-)
0
BOEING39Author Commented:
I appreciate everyone's input on this.  Paul thanks for the edification and finite detail in your response.   I learned something and it is now functioning as needed.
0
PortletPaulfreelancerCommented:
Really pleased it helped. Cheers Paul

ps, see: "Beware of Between"
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.