Microsoft KB Archive/110906

= You receive an error message when you run an SQL SELECT statement that has a UNION clause and an ORDER BY clause =

Article ID: 110906

Article Last Modified on 3/18/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft FoxPro 2.5b
 * Microsoft FoxPro 2.5a
 * Microsoft FoxPro 2.5b
 * Microsoft FoxPro 2.5b for MS-DOS
 * Microsoft FoxPro 2.5a
 * Microsoft FoxPro 2.5b for MS-DOS

-



This article was previously published under Q110906



SYMPTOMS
When you run an SQL SELECT statement that has a UNION clause and an ORDER BY clause, you may receive the following error message if the ORDER BY clause references a field name:

SQL Invalid ORDER BY

In Microsoft Visual FoxPro, you may receive the following error message:

SQL: Order by Clause is Invalid

For example, this error will occur if you type the following SQL SELECT statements in the Command window. SELECT * FROM customer ; UNION ; SELECT * FROM customer_a ORDER BY cno



CAUSE
This error message is the correct response according to page L3-872 of the "Language Reference."



RESOLUTION
To resolve this problem, replace the ORDER BY  clause with an ORDER BY   clause. In the ORDER BY  clause, the numeric expression indicates the number of the column to order the output by. Only the final SQL SELECT statement can have an ORDER BY clause.

The following example shows the correct syntax. ** This example will order the output by the first output field. SELECT * FROM customer ; UNION ; SELECT * FROM customer_a ORDER BY 1

Additional query words: VFoxWin FoxDos FoxWin

Keywords: kberrmsg KB110906

-

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

© Microsoft Corporation. All rights reserved.