At 05:41 a.m. 3/03/2013, Andrea Raimondi wrote:

>Hello!
>
>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.

This query is trying to be EITHER a union query, a derived table query or a CTE query but it doesn't meet the syntax of any of them. Let's assume you intend it to be a union query:
1. SELECT * FROM (select...) is not valid
2. A UNION query gets the column identifiers and their data types from the first subset, so supply column aliases only in the first subset of the union
3. if a column in the first subset is a literal, then you have to cast the literal to a type that is compatible with the corresponding fields in the other subset(s) -- ** don't forget to quote strings! **
4. I would also use different relation aliases in each subset for the joins to DEPARTMENT.
5. You can't order by a column name in a union set, only by column numbers
6. (And you can't order by a column alias name at all)

Let's assume that ADM.ADMIN_LEVEL is char(8):

SELECT EMP. ID, ( EMP.SURNAME||', '||EMP.FIRSTNAME ) FULL_NAME,
DEP.department_name DEPARTMENT,
cast ('( 0 ) _A' as char(8) ADMIN_LEVEL
FROM EMPLOYEES EMP
LEFT OUTER JOIN DEPARTMENTS DEP ON DEP.ID = EMP.DEPARTMENTID
UNION
SELECT ADM. ID, ( ADM.SURNAME||', '||ADM.FIRSTNAME ), DEP1.department_name,
ADM.ADMIN_LEVEL
FROM ADMINISTRATORS ADM
LEFT OUTER JOIN DEPARTMENTS DEP1 ON DEP1.ID = ADM.DEPARTMENTID

ORDER BY 1, 4

If you intended it to be a derived table or a CTE query then consult the Language Reference to work out what you need to do to get the desired result ;-)
Also, if you have employees that are also administrators and you want both rows in the set then you need UNION ALL.


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.ibphoenix.com/products/books/firebird_book
__________________________________________________________________