Knowledge Base

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

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.

MORE INFORMATION

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