Microsoft KB Archive/157188

= String functions cannot be used with the SQL-SELECT ORDER BY clause in Visual FoxPro =

Article ID: 157188

Article Last Modified on 3/18/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 9.0 Professional Edition

-



This article was previously published under Q157188



SYMPTOMS
The following string functions cannot be used with the ORDER BY clause of the SQL-SELECT statement:
 * LEFT
 * RIGHT
 * SUBSTR

If you use the previous string functions with the ORDER BY clause, you receive the following error message:

SQL Column is not found

In Visual FoxPro 8.0 and in Visual FoxPro 9.0, you receive the following error message:

SQL ORDER BY clause is invalid.



CAUSE
ORDER BY supports only columns either by name or number, it does not support any other expressions.



WORKAROUND
Create a dummy column using the string function and then order by that column, as in the following example: SELECT *,LEFT(Prod_name,5) AS x FROM PRODUCTS ORDER BY x



STATUS
This behavior is by design.



Steps to Reproduce Behavior
 Open Testdata database in the HOME+"Samples\Data\" directory, for Visual FoxPro 6.0 goes to \\Program Files\Microsoft Visual Studio \Common\Samples\Data.  Issue the following command: SELECT * FROM Products ORDER BY LEFT(Prod_name,5) 

Additional query words: kbdse VFoxWin

Keywords: KB157188

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.