Create a Union Query
Create a union query to combine two or more queries into one result set.
You can combine queries that use different data sources. For example, you can combine a query that returns data from a dimensional data source with a query that returns data from a relational data source.
Before you begin
To combine two queries, the following conditions must be met:
- The two queries must have the same number of data items.
- The data items must have compatible types and appear in the same
For numeric data types, integer, float, double, and decimal are compatible.
For string data types, char, varChar, and longVarChar are compatible.
For binary data types, binary and varBinary are compatible.
Date data types must match exactly.
- Click the Queries icon , and click Queries.
Click the Toolbox icon , and do the following:
- Drag Query to the work area.
- Drag Union, Intersect, or
Except to the right of the query.
Two drop zones appear to the right of the operator.
- Drag a Query object to each drop zone.
Two queries are created in the work area, and a shortcut to each query appears in the drop zones.
- Double-click each query that makes up the union query and add data items to the query.
- Return to the Queries work area.
- Click the set operator that you added in step 2.
- Click the Show properties icon , and In the Properties pane, set the Duplicates property to remove or preserve duplicate rows.
Double-click the Projection list property.
The projection list shows the list of projected data items for the set operation.
- To automatically produce the list of projected data items,
click Automatically generated.
IBM® Cognos® Analytics - Reporting generates the projection list using only one of the two queries in the union.
- To add, delete, move, or rename data items in the projection list, click Manual and make the changes.
- Double-click the union query.
- Click the Data icon , and drag data items to the Data Items pane.