FIX: SQL SELECT Not Working on Ascending/Descending Indexes

2.50    | 2.50

The information in this article applies to:

• Microsoft FoxPro for Windows, version 2.5
• Microsoft FoxPro for MS-DOS, version 2.5

SYMPTOMS

The SELECT SQL command is used to query one or more databases for information based on certain criteria. This command is Rushmore optimizable, so it will take advantage of any indexes that are already created for the database(s) involved in the query. However, in both FoxPro 2.5 for Windows and FoxPro 2.5 for MS-DOS, an incorrect number of records may be returned when SQL SELECT is used to join the tables.

This problem occurs when you are joining two databases and one has the order set to a ascending index and the other has the order set to a descending index. This problem also occurs when you are using RQBE.

STATUS

Microsoft has confirmed this to be a problem in FoxPro version 2.5 for Windows and FoxPro version 2.5 for MS-DOS. This problem was corrected in FoxPro version 2.5a for Windows and FoxPro version 2.5a for MS-DOS.

MORE INFORMATION

The code below demonstrates this problem. It is using the CUSTOMER and INVOICES databases from the TUTORIAL subdirectory.

   SET DEFAULT to \FOXPRO25\TUTORIAL  (or \FOXPROW\TUTORIAL)
   USE customer
   INDEX ON cno TAG cno ASCENDING
   SET ORDER TO TAG cno
   SELECT B

   USE invoices
   INDEX ON cno TAG cno DESCENDING
   SET ORDER TO TAG cno

   SELECT *;
   FROM Customer,Invoices;
   WHERE Customer.cno = Invoices.cno ;
   AND Customer.cno = "A2695"