I found in Interbase that often (?always) the ORDER BY in a UNION needed
the ordinal fields instead of the field names. Looks it is same in
Firebird.

RBS

> The individual statements that make up this union sort fine. When I
> combine them and sort, it fails with a prepare error. All columns are
> the same type and name.
>
> works
> SELECT RANK, WHLSLR, DISTCTR FROM V_RPL_TO_ORDER
> ORDER BY RANK, WHLSLR, DISTCTR
>
> works
> SELECT RANK, WHLSLR, DISTCTR FROM V_RPL_TO_ORDER_ALT
> ORDER BY RANK, WHLSLR, DISTCTR
>
> works
> SELECT RANK, WHLSLR, DISTCTR FROM V_RPL_TO_ORDER
> UNION
> SELECT RANK, WHLSLR, DISTCTR FROM V_RPL_TO_ORDER_ALT
> ORDER BY 1, 2, 3
>
> fails
> SELECT RANK, WHLSLR, DISTCTR FROM V_RPL_TO_ORDER
> UNION
> SELECT RANK, WHLSLR, DISTCTR FROM V_RPL_TO_ORDER_ALT
> ORDER BY RANK, WHLSLR, DISTCTR
>
> PREPARE ERROR:
> ISC ERROR CODE:335544569
>
> ISC ERROR MESSAGE:
> Dynamic SQL Error
> SQL error code = -104
> Invalid command
> invalid ORDER BY clause
>
> Disclaimer: This message (including attachments) is confidential and may
> be privileged. If you have received it by mistake please notify the sender
> by return e-mail and delete this message from your system. Any
> unauthorized use or dissemination of this message in whole or in part is
> strictly prohibited. Please note that e-mails are susceptible to change.
> RxStrategies, Inc. shall not be liable for the improper or incomplete
> transmission of the information contained in this communication or for any
> delay in its receipt or damage to your system. RxStrategies, Inc. does not
> guarantee that the integrity of this communication has been maintained nor
> that this communication is free from viruses, interceptions or
> interference.
>
>
>