Link to home
Create AccountLog in
Avatar of BOEING39
BOEING39

asked on

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
Avatar of N-W
N-W
Flag of Australia image

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.
Avatar of kaufmed
"&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.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
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.
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).
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.
That pretty much covers everything ;-)
Avatar of BOEING39
BOEING39

ASKER

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.
Really pleased it helped. Cheers Paul

ps, see: "Beware of Between"