🚀 go-pugleaf

On Tue, 21 Jul 1998 17:12:34 GMT, ncbuyukmihci@ucdavis.edu (Nedim
Buyukmihci) wrote:

>VdB 7.01 (b1419), Win95
>
>A particular table has 543 records having zipcodes of 94000 to 96200
>inclusive (determined by various counts).
>
>If set the indexName to Zipcode and then do a rowset.setRange(low,
>high), get 543 as expected.
>
>But, if you try to do a select * statement with the two conditions:
>
>  select * from <table> where <field> >= '94000' and <field> <=
>'96200'
>
>this gives only 199 records. Why would there be this discrepancy?

It is not immediately clear what might be going wrong. Some methodical
testing and tweaking might reveal this.

The column used as the basis of the WHERE clause filtering is of CHAR type?
If so, your enclosing the comparison values in quotation marks would be
appropriate. If the column's data type was other than CHAR, I would expect
an error anyway. What is the width of the column? If greater than five (the
width of your comparison values) are the column's values right- or
left-justified within the column?

What result do you get with a test SQL statement that only folters using
one end of the range? For example:

  SELECT COUNT(*)
  FROM "YourTable.dbf" Y
  WHERE (Y.YourField >= "94000")

Then try the opposite end of the range:

  SELECT COUNT(*)
  FROM "YourTable.dbf" Y
  WHERE (Y.YourField <= "96200")

What is the result of using the BETWEEN predicate, the more common means of
expressing a range of comparison values in SQL statements?

  SELECT COUNT(*)
  FROM "YourTable.dbf" Y
  WHERE (Y.YourField BETWEEN "94000" AND "96200")

Do you see different effects when the SQL function TRIM is applied to the
column used in the filter comparison?

  SELECT COUNT(*)
  FROM "YourTable.dbf" Y
  WHERE (TRIM(BOTH " " FROM Y.YourField) BETWEEN "94000" AND "96200")

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