You can use a union to add constant values into a result set or to combine different, un-linked tables into one result set.
The fields in each union must be identical - field names are taken from the first query in the union.
A Query with unions can be ordered by adding an 'order by' clause at the end of the query using the format: order by 1(where 1 = column number)
The following example illustrates how a union is used to add an 'All' option to a list of Item Categories.
select -1 as ID, 'All' as CATEGORY from RDB$DATABASE union select MATCATID, NAME from MATCAT order by 2
The following example illustrates how a Transaction's Materials and Services (Costs) can be selected into one result set.
--Materials select M.QUOTEOUTID as ID, 'Material' as COSTTYPE, M.DESCRIPTION, M.QTY, M.TOTAL from QUOTEOUTSOURCE M join VATITEMS I on M.VATITEMID = I.VATITEMID join LEDGER L on I.TRANSID = L.TRANSID where L.ENTRYTYPEID = 1 -- ENTRYTYPEID for Quotations and L.DOCID = 18 -- Transaction No. union --Services select S.QUOTEFINID, 'Service', S.DESCRIPTION, S.QTY, S.TOTAL from QUOTEFIN S join VATITEMS I on S.VATITEMID = I.VATITEMID join LEDGER L on I.TRANSID = L.TRANSID where L.ENTRYTYPEID = 1 -- ENTRYTYPEID for Quotations and L.DOCID = 18 -- Transaction No. order by 1,2