> I am sure I a missing something, just unsure what :)
>
> This is my query:
>
> SELECT *
> FROM (
> SELECT EMP. ID, ( EMP.SURNAME||', '||EMP.FIRSTNAME ) FULL_NAME,
> DEP.department_name DEPARTMENT,
> ( 0 ) _A
> FROM EMPLOYEES EMP LEFT
> OUTER JOIN DEPARTMENTS DEP ON DEP.ID = EMP.DEPARTMENTID
> UNION
> SELECT ADM. ID, ( ADM.SURNAME||', '||ADM.FIRSTNAME ) FULL_NAME,
> DEP.department_name DEPARTMENT, ADM.ADMIN_LEVEL FROM
> ADMINISTRATORS ADM LEFT OUTER JOIN DEPARTMENTS DEP ON DEP.ID =
> ADM.DEPARTMENTID
> )
> ORDER BY FULL_NAME, 4
>
> I'd like to assign a field name to the last column in the upper part of the
> Union, but Firebird doesn't seem too keen. What am I missing? Must be
> something obvious, surely, but it totally fails me.

SQL Names (Column/Field/Alias/Table/Views/Functions) must start with an Alpha character (99% sure this is by SQL standard).

So, "_A" is not a valid name, but "A_" is.


Sean

P.S. I always ensure that if I use a UNION that I use the same column aliases for all the SELECT statements, it helps me ensure that the SELECT fields are matching up in the correct positions.

P.P.S. UNION uses the Column Aliases of the first SELECT for the column names of the result set, but uses the columns by position of the subsequent SELECTs -- which explains why the statement is failing, if the SELECT were re-ordered, the last column would have the name "ADMIN_LEVEL" and the statement would be valid.