Join table - Connect_CDC - connect_cdc_mimix_share - Latest

Join tables extend table lookup by allowing multiple columns to be retrieved from a secondary join table. When the row is selected from the secondary join table, the columns in the join table become accessible for use in target column expressions.

Note the following:

• Connect CDC allow the source row to be sent even if the join fails. To enable this feature, check Allow outer join on the Join Tables dialog box. See Specify the join information on the join table dialog.

• Connect CDC allows the source row to be sent even if the join fails to select a row.

• No column in the join table may be referenced in a gate condition where you specify a Boolean expression whose results, if true, allow data transmission of the row based on a valid expression for the column.

• Join columns can only be referenced in a result expression.

Join tables function as follows:

• Any column from the join table can be selected as a join column.

• A source table can optionally have more than one join table associated with it. Multiple join tables can be used.

• Joins may be defined in one direction only, that is, from trigger table to join table, for example from T1 to T2. You cannot define a second join from T2 back to T1.

• If the join table is deleted, at execution time the request is terminated and an alert is generated.

• At execution time, if more than 1 row is returned from a join table, a warning message is issued. Only the first row returned from the join table query will be used.

• If a row is not found in any join table whose column(s) are used in an expression of a mapping, the mapping is ignored for the trigger row: The target row associated with this mapping is not output.

• Changes to data in joined tables do NOT trigger replication of this mapping.

• Join tables are available for all database platforms supported in Connect CDC’s current version.

• Tables referenced in the join statement must be within the same server.

• Datatypes on joined columns must be compatible.

Columns from joined tables, once defined for a source table, are used (“specified”) in Target Column Expressions. No default values are specified; the convert null to( ) functions can be used, as for any null value.

You can modify the expression and add additional columns from the join table, if needed.

In the following diagram, T1 is a trigger table, which has been joined to T2. When an update is made to T1, a value from columns in the joined tables are replicated along with columns from the trigger table.

 

In the example used on the following pages, the trigger table Employee contains employee information in these columns:

The Employee table is to be joined to the Address table, consisting of these columns:

The join table is used in three expressions:

• Concatenation of ADDRESS_1 and ADDRESS_2  into a target column ADDRESS

• Concatenation of CITY, STATE, and PROVINCE into a target column LOCATION

• ZIP_CODE to target column ZIP_CODE