At 02:04 AM 15/03/2008, you wrote:

>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

Correct. A UNION is by nature a merging of two or more sets. The fields in the contributing sets can come from anywhere, as long as the data types match across the output structure.

So - while your particular query happens to have column names that match, it is not a rule for unioned sets that this be so; in fact, it is a implementation rule that it must not rely on this being so. The field names extracted (or assigned) in the first contributing subset determine the field names for the output set but there is no way to refer to these names by identifier, since the optimizer cannot distinguish which source field the identifier would refer to. Hence, you always define the ordering criteria for unioned sets by the degree number[s] of the output field[s]. (degree = position of the output field in the set, counting from the left, starting at 1).

./heLen