Microsoft KB Archive/104330

{| = FIX: SQL SELECT Not Working on Ascending/Descending Indexes =
 * width="100%"|

ID: Q104330

2.50   | 2.50 WINDOWS | MS-DOS kbprg kbfixlist kbbuglist 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" 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
 * }