Working with except transformations

You use except transformations to include values that are in one query or table and not in the other query or table to the final result set.

About this task

Except 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 except two columns from two different queries, rows from only the first selected columns are included 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 an except transformation in the analytical query structure:

Procedure

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