Hi Lourenço,

The ORDER BY clause for a UNION can´t used with column name. You
have use the number.

SELECT FIELD_A X FROM TALBLE_A
WHERE FIELD_A > 100
UNION
SELECT FIELD_B X FROM TALBLE_B
WHERE FIELD_B > 100
ORDER BY 1

This number represent the position its at SELECT clause.

> ----- Mensagem original -----
> De: Lourenço de Paula [SMTP:lourenco.d@...]
> Enviada em: sexta-feira, 14 de março de 2003 11:03
> Para: ib-support@yahoogroups.com
> Assunto: [ib-support] Union and order by
>
> Hello
>
> I'm trying to execute the follow statement:
>
> SELECT FIELD_A X FROM TALBLE_A
> WHERE FIELD_A > 100
> UNION
> SELECT FIELD_B X FROM TALBLE_B
> WHERE FIELD_B > 100
> ORDER BY X
>
> It´s a exemple, but when I execute the SQL statement the Firebird
> returns:
> "Invalid token. Dynamic SQL Error. SQL error code = -104. Invalid
> command. invalid ORDER BY clause. "
>
> If I remove the ORDER BY clause it executes sucessfully.
>
> Can I use order by clause with UNION or it is not supported?
>
> Thanks.
>
> Lourenço de Paula
> lourenco.d@...
> Desenvolvimento Revenda
> CNP Engenharia de Sistemas S.A. - Brazil
>
> [Non-text portions of this message have been removed]
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>