SQL Parentheses and "AND" bug?
ERose at LeapFrog.com
Tue Jul 8 09:37:39 CDT 2003
When I was working on Valentina and developing cross platform (Access on
Windows, Valentina on Mac) I came up with some rules...
Here are the rules:
1) if fields are compared to constants, all fields from the same table must
be enclosed in parentheses. Example:
( (STUDENT_ACTIVITY_STATUS.ID = 1) AND
(STUDENT_ACTIVITY_STATUS.ACTIVITY_CODE = 'SR21') )
2) everything except parenthesis must have spaces around it:
NOT: (STUDENT_ACTIVITY_STATUS.ID=1) BUT (STUDENT_ACTIVITY_STATUS.ID = 1)
this sometimes isn't necessary and sometimes is, to be safe just do it
3) when 2 tables have 2 or more links, all those links must be in
( (STUDENT_ACTIVITY_QUESTION_STATUS.SAS_ID =
AND (STUDENT_ACTIVITY_QUESTION_STATUS.ACTIVITY_CODE =
4) it is best for speed's sake to have the compare to constant at the start
of the SQL and other statements in order of decreasing sifting of the data.
5) ALL fields in the ORDER BY must be in the SELECT fields. This is not
true for Access, but is true for Valentina. Solution: put the fields at the
end of the SELECT fields (so it won't impact the ordering).
6) No JOINS using SQL '92 syntax, no Access queries, everything in:
SELECT, FROM, WHERE, ORDER BY form
Of course, with the new SQL parser, this will ALL become obsolete (THANK
Leapster Emulator/Optimizer Guy
From: Francois Van Lerberghe [mailto:fvanlerberghe at freegates.be]
Sent: Monday, July 07, 2003 11:10 PM
To: Valentina Developers
Subject: Re: SQL Parentheses and "AND" bug?
le 7-07-03 21:55, erobar at earthlink.net <erobar at earthlink.net> a écrit :
> Note: the ONLY difference is that I've added parentheses around the last 2
> criteria, which produces the right results. With the first SQL statement,
> returns items where table_B.field_B is 1 through 5! So, the third
> seems to be obliterated somehow....
I think it's a long standing problem.
And I think it's even better to group and enclose in parentheses all
conditions of the same table, not only the last
Thus I'd write this query as this :
select count(*) from table_A, table_B where
(table_A.field_A >= 3 and table_A.field_A <= 5) and
(table_B.field_B >= 3 and table_B.field_B <= 10)
François Van Lerberghe
Thier Monty, 15A
Valentina mailing list
Valentina at lists.macserve.net
More information about the Valentina