Join table - Connect_CDC - connect_cdc_mimix_share - 5 - 5.8

Connect CDC Advanced User Guide

Product type
Software
Portfolio
Integrate
Product family
Connect
Product
Connect > Connect CDC (MIMIX Share)
Version
5.8
Language
English
Product name
Connect CDC
Title
Connect CDC Advanced User Guide
Copyright
2023
First publish date
2003
ft:lastEdition
2023-09-27
ft:lastPublication
2023-09-27T18:12:55.651884

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.

Note: This is the same as any column mapping
  • 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:

Column Name

Type

EMPNO

number

EMP_NAME

varchar

JOB

varchar

EMP_MANAGER

varchar

SALARY

number

DEPARTMENT

number

ORDERID

number

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

Column Name

Type

EMPNO

number

ADDRESS_1

varchar

ADDRESS_2

varchar

CITY

varchar

STATE

varchar

PROVINCE

varchar

ZIP_CODE

int

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