Use the allow outer join feature - Connect_CDC - connect_cdc_mimix_share - 6.x

Share allows the source row to be sent even if the join fails to select a row. This feature is enabled in the Join Tables dialog when you select Allow outer join.

To access the Join Tables dialog:

1. Use the Join popup menu item on an available table.

   

2. Use the Join button on the Sending Table Properties, Table tab.
   Note:  A join is a property of an Available Table not a Sending Table. It can be defined once and stays with the Available Table even if it is not a Sending Table at the current time.
3. Specify the join information.

   The Join Table field contains a list of all the tables in the Available Tables cache. You see in the example, that we are joining to a table qatest.dbo.ADDRESS that contains employee address information.

   1. Select Allow outer join, to allow the source row to be sent even if the join fails.

      

   2. In Join table, select the table.
   3. If required, select Allow non-unique column join, which enables you to select any column from the join table as a join column. At least one indexed column must be selected to avoid a table scan at execution time.
   4. If Allow non-unique column join is not selected, select the index.
   5. When a table is selected, Join table indexes is filled in automatically. If Allow non-unique column join was not selected, only unique indexes are displayed. If Allow non-unique column join was selected, all indexes are displayed. Any displayed index is a potential join index, but only one join can be defined between any two tables.

   6. When an index is selected, the grid fills in. At present, the only method available is Copy column.

   7. Define how each join column value is to be formed from the trigger table columns; this may currently be only a single index, which may consist of more than one column. Click the blank field under Source Data to access a list of compatible columns from the source table. If Allow non-unique column join is selected, any column from any index may be used.

      If no join data existed, you may manually map the join columns, or use Defaults to try to automap by name.

   8. If you selected Allow non-unique column join, an additional Indexed column displays in the grid. The checkbox is selected if the column is defined as the first column in at least one index on the join table. It is the first column of the index to ensure that the index is usable when the join query is executed. In most databases, if only the secondary columns of a multi-column index are specified in the where clause, the index is not used, resulting in a table scan.

   9. Use Apply to validate and save the current grid data. After you use Apply, you may define additional join conditions to other tables.

When the join condition is defined, all columns in the joined table are available to be used in an expression. Currently, all join columns used in an expression must be fully qualified by the table name or user-assigned alias.

Once join columns are mapped, you can use Unmap all to remove the join definition.