FIX: SQL SELECT Not Working on Ascending/Descending Indexes

ID: Q104330 

2.50    | 2.50
WINDOWS | MS-DOS kbprg kbfixlist kbbuglist

The information in this article applies to:

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"
The correct result should be a Browse window containing five records. However, the result that is actually returned contains only four records.

Additional reference words: FoxDos FoxWin buglist2.50 fixlist2.50a 2.50 2.50a KBCategory: kbprg kbfixlist kbbuglist KBSubcategory: FxtoolRqbe 

Keywords          : FxtoolRqbe kbbuglist kbfixlist
Version           : 2.50    | 2.50
Platform          : MS-DOS WINDOWS
Solution Type     : kbfix

Last Reviewed: September 24, 1997