Skip to content

Union

 

You can use a union to add constant values into a result set or to combine different, un-linked tables into one result set.

Notes

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