> Roy Damman wrote:
>
> Third query:
> "select "TESTID", "NAME" from test
> union select "TESTID", "NAME" from test order by upper(2)"
> Answer (Unexpected):
> An error message: ISC ERROR CODE:335544569 ISC ERROR MESSAGE:
> Dynamic SQL Error
> SQL error code = -104
> Invalid command
> invalid ORDER BY clause
>
> Especially the third query is annoying me. Is this according to the
> standard or is it a bug? Why is does "order by upper(2)" not work
> with an union and work without an union?
>
> Maybe I am wrong but I always thought the "order by" is applied on the
> results of the two selects in both the second and third union query.
> And the order by clause does work in the second query (also an union
> query) "order by 2" and does not work in the third query "order by
> upper(2)". The only difference is the use of "upper()" which does work
> with the first query with only one select.

This puzzles me, although I'm uncertain whether I'm puzzled by upper(2) not working with UNION or puzzled by upper(2) working without UNION.

Here, I first wrote that it works when using a CTE, but then I discovered that it only works in the sense that it produces a result set, the ordering seems random. At least, the following simple SQL produces incorrect ordering:

Select rdb$collation_name
From rdb$collations
Order by upper(1)

This on Firebird 2.5.1.

Hence, I think what you want to order by must be selected, i.e.

With tmp as
(select "TESTID", "NAME", upper(name) as MyOrdering from test
union
select "TESTID", "NAME", upper(name) from test order by 3)

select "TESTID", "NAME"
from tmp

(I haven't tested this last query, just expects it to be returned correctly ordered).

HTH,
Set