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

The information in this article applies to:

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:

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.

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

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

This behavior is by design.

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)

Modification Type:	Major	Last Reviewed:	3/18/2005
Keywords:	KB157188 kbAudDeveloper