🚀 go-pugleaf

On Fri, 5 Jun 1998 23:05:58 -0700, "Jason Ortner" <jortner@onramp113.org>
wrote:

[...]

>Below is one form of the query.  If you change the order in which you
>call the fields, the query results seem to change.  I am not a expert
>in this and am just trying to understand why and how it works so I can
>use it more effectively.

[...]

>form.fe = new query()
>   form.fe.sql = "select
>headfn,headln,heelfn,heelln,dateofrope,drawnum;
>   from rope1.dbf rope1;
>where (headfn = '&hdfn');
>     AND (headln = '&hdln');
>     OR (heelfn = '&hefn');
>     and (heelln = '&heln');
>     OR (headln = '&heln')"

OK, now we're getting somewhere. I do not believe the problem you are
encountering has anything to do with the order in which the fields are used
in the WHERE clause. I think it has everything to do with the order in
which the logical operators are used in connecting the multiple conditions.

Understand that, like in arithmetic operations, there is an order of
precedence in logical operators. Specifically, OR conditions will be
considered first and then AND conditions. You can override this using
strategically placed parentheses to force conditions to be considered in
the order you desire. The SELECT statement below retrieves all rows where
the SHAPE column is "round" *and* the COLOR "blue". It also returns those
rows where the COLOR column is "red", regardless of the value in the SHAPE
column (such as "triangle"). It would also not return rows where the SHAPE
is "round" *and* the COLOR anything but "blue" or the COLOR is "blue" and
the SHAPE anything but "round".

  SELECT shape, color, cost
  FROM objects
  WHERE ((shape = "round") AND (color = "blue")) OR
    (color = "red")

If you leave out the parentheses, the default order of precedence is used
and the logic changes. The statement below, a variation on the above
statement, would return rows where the SHAPE is "square" *and* the COLOR is
"blue". It would also return rows where the SHAPE is "square" *and* the
COLOR is "red". But unlike the preceding statement, it would not return
rows where the COLOR is "red" and the SHAPE "triangle".

  SELECT shape, color, cost
  FROM objects
  WHERE shape = "round" AND color = "blue" OR
    color = "red"

What you need to do is reconsider the relationships between your multiple
conditions, and use parentheses to group pairs or sets of related
conditions. I am making a guess with statement below, but it could get you
on your way.

  SELECT headfn, headln, heelfn, heelln, dateofrope, drawnum
  FROM rope1.dbf rope1
  WHERE ((headfn = '&hdfn') AND (headln = '&hdln')) OR
        ((heelfn = '&hefn') AND (heelln = '&heln')) OR
        (headln = '&heln')

With this arrangement, you have three minor conditions (see diagram below),
each of which yields an evaluation result of True or False. Because of the
addition of the parentheses, each of these three AND condition pairings
will be evaluated first to yield their boolean results, before the major OR
condition comparisons.

  #1 (headfn = '&hdfn') AND (headln = '&hdln')
  #2 (heelfn = '&hefn') AND (heelln = '&heln')
  #3 (headln = '&heln')

After each of these three minor conditions is evaluated, they are compared
to each other using OR conditions:

  Condition #1 OR
  Condition #2 OR
  Condition #3

This may or may not be what you intended, but it should give you the
perspective to analyze the true relationships between your multiple
conditions and give you the means to arrange them to produce the result set
you intended. Do not for a minute think that this is now or always should
be a light task. It seldom is. And the more conditions with mixed OR and
AND operators you have, the greater the headache trying to arrange them
right.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "Knowledge comes, but wisdom lingers."
Technical Publications                -- Alfred, Lord Tennyson (1809-1914)
INPRISE Corporation
http://www.inprise.com/delphi