Working with intersect transformations

You use intersect transformations to include common values that exist in both the queries or tables to the final result set.

About this task

Intersect 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 intersect two columns from two different queries, only common 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 column 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 an intersect transformation in the analytical query structure:

Procedure

  1. In the Analytical query structure, select an intersect transformation node.
    In the Item editor pane, you can view the items of the intersect 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 intersect 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 intersect condition is added to the Result Columns list.
    4. Repeat this procedure for each intersect condition that you want to add.
    Note: Intersect 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 intersect 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 intersect transformation with objects that contain similar names, you can try to automatically create the appropriate intersect 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 intersect 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 intersect transformation can be run on a database to accelerate the execution of this query. In the Output view, you can see that SQL INTERSECT is applied for such transformations. Generally, intersect 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 intersect transformations, you must ensure that the data in the columns, on which intersect 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 intersect transformation is applied correctly, you can choose to use the database or QMF format functions to format the data.