Working with union transformations

You use union transformations to add unique result set columns from different queries or tables to the final result set.

About this task

Union transformations allow you to combine result set columns from different queries or tables from either the same or different data sources to one result set. When you perform union on two columns from two different queries, unique rows from both of the selected columns are combined in one column in the final query results. You can use Collate Option for case sensitive or case insensitive comparison of data. The Collate option gets activated only when the Result Columns entry is of the type string, var char, or text. The column in the First list determines the name of the final query result set column, and the rows from the column is displayed.

To configure a union transformation in the analytical query structure:

Procedure

  1. In the Analytical query structure, select an union transformation node.
    In the Item editor pane, you can view the items of the union transformation. The columns from the source data set in the upper node are displayed in the First list. The columns from the source data set in the lower node are displayed in the Second list.
  2. Optional: If the selected union transformation is not populated yet, add queries or tables into <none> nodes. For more information, see Populating analytical query transformations.
  3. Specify the query result columns that you want to add together.
    1. Select a column from the First list.
    2. Select a column from the Second list.
    3. Click Add Column above the Result Columns list.
      The new union condition is added to the Result Columns list.
    4. Repeat this procedure for each union condition that you want to add.
    Note: Union of large object data types LOB, CLOB, and BLOB is not supported. An error message is displayed when you attempt to include a LOB, CLOB, or BLOB column in union transformations.
  4. Optional: You can configure the result to be displayed in a case sensitive or case insensitive manner by clicking Collate Option. Specify Collate Option for each result column by selecting case sensitive or case insensitive option from the drop-down provided.
    Note: Collate Option button is enabled when at least one of the result column is of the character data type.
  5. If you are populating the union transformation with objects that contain similar names, you can try to automatically create the appropriate union conditions by clicking Automatch.
    If there are any possible auto-matches, they are displayed in the Result Columns list.
    Note: Clicking Automatch will erase any union conditions that you previously set.
  6. To include a column without matching it with any column from the other data set, select this column and select <unmatched> from the second column list. Click Add Column above the Result Columns list.
  7. Optional: To add another transformation to the current analytical query structure, follow the procedure described in Building analytical query structures.

Results

Whether it is the only transformation in an analytical query or one of many nodes in a complex structure, an union transformation can be run on a database to accelerate the execution of this query. In the Output view, you can see that SQL UNION is applied for such transformations. Generally, union transformations are run on databases if they contain queries with simple SQL statements using only the SELECT operator and retrieving data from one database. Db2 database queries can also apply SQL statements that involve join conditions, sort conditions, row conditions, calculated columns, simple filtering, groups, and categorizations.
Note: When performing union transformations, you must ensure that the data in the columns, on which union is performed, are in the same format. That is:
  • columns with textual data should be in the same case to be considered unique. For example, "IBM" and "ibm" are considered as distinct values.
  • columns with numerical data should have the same precision and scale to be considered unique. For example, "12.010" and "0000012.01" are considered as distinct values.
  • columns with date data should be in the same format to be considered unique. For example, "12/02/1980" and "12/02/80" are considered as distinct values.
  • columns with currency data should be in the same format to be considered unique. For example, "$100" and "$100.00" are considered as distinct values.
To ensure that the union transformation is applied correctly, you can choose to use the database or QMF format functions to format the data.