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

The information in this article applies to:

Microsoft Visual FoxPro for Windows 3.0
Microsoft FoxPro for Windows 2.5
Microsoft FoxPro for Windows 2.5a
Microsoft FoxPro for Windows 2.5b
Microsoft FoxPro for MS-DOS 2.5
Microsoft FoxPro for MS-DOS 2.5a
Microsoft FoxPro for MS-DOS 2.5b

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 character expression clause with an ORDER BY numeric expression clause. In the ORDER BY numeric expression 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

Modification Type:	Major	Last Reviewed:	3/18/2005
Keywords:	kberrmsg KB110906