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 order.

    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.

Procedure

  1. Click the Queries icon source tab, and click Queries.
  2. Click the Toolbox icon 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.

  3. Double-click each query that makes up the union query and add data items to the query.
  4. Return to the Queries work area.
  5. Click the set operator that you added in step 2.
  6. Click the Show properties icon Show properties icon, and In the Properties pane, set the Duplicates property to remove or preserve duplicate rows.
  7. Double-click the Projection list property.

    The projection list shows the list of projected data items for the set operation.

  8. 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.

  9. To add, delete, move, or rename data items in the projection list, click Manual and make the changes.
  10. Double-click the union query.
  11. Click the Data icon Data icon, and drag data items to the Data Items pane.

Results

The union query is complete. You can now link the union query to a data container in the layout.