Article ID: 110906 - Last Review: March 18, 2005 - Revision: 3.1

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

View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
This article was previously published under Q110906
Expand all | Collapse all

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
Back to the top

CAUSE

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

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
Back to the top
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
Back to the top
Keywords: 
kberrmsg KB110906
Back to the top