PRB: String Functions Cannot Be Used with SQL ORDER BY Clause

• Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 6.0

SYMPTOMS

String functions such as LEFT(), RIGHT(), and SUBSTR() cannot be used with the ORDER BY clause of the SQL - SELECT statement. Doing so results in the following error message:

SQL Column '' is not found

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

1. Open Testdata database in the HOME()+"Samples\Data\" directory, for Visual FoxPro 6.0 goes to \\Program Files\Microsoft Visual Studio \Common\Samples\Data.



2. Issue the following command:

   
         SELECT * FROM Products ORDER BY LEFT(Prod_name,5) 

Additional query words: kbdse VFoxWin

Keywords : kbVFp300 kbVFp500 kbVFp600 FxprgSql
Version : WINDOWS:3.0,3.0b,5.0,6.0
Platform : WINDOWS
Issue type :