Defining a Join Condition

Depending on the type of join selected, the Data Integration Service either adds the row to the result set or discards the row. The Joiner transformation produces result sets based on the join type, condition, and input data sources.

Before you define a join condition, verify that the master and detail sources are configured for optimal performance. During a mapping run, the Data Integration Service compares each row of the master source against the detail source. To improve performance for an unsorted Joiner transformation, use the source with fewer rows as the master source. To improve performance for a sorted Joiner transformation, use the source with fewer duplicate key values as the master.

Use one or more ports from the input sources of a Joiner transformation in the join condition. Additional ports increase the time necessary to join two sources. The order of the ports in the condition can impact the performance of the Joiner transformation. If you use multiple ports in the join condition, the Data Integration Service compares the ports in the order you specify.

If you join Char and Varchar datatypes, the Data Integration Service counts any spaces that pad Char values as part of the string:

The Char value is “abcd" padded with 36 blank spaces, and the Data Integration Service does not join the two fields because the Char field contains trailing spaces.

You can define a simple or advanced condition type. You can also define an expression parameter. An expression parameter is a parameter that contains the join expression. You can change the parameter value at run time with a mapping parameter.

A simple condition includes one or more conditions that compare the specified master and detail sources. A simple condition must use the following format:

For a sorted Joiner transformation, the condition must use the equality operator.

For an unsorted Joiner transformation, the condition can use any of the following operators: =, !=, >, >=, <, <=.

For example, if two sources with tables called EMPLOYEE_AGE and EMPLOYEE_POSITION both contain employee ID numbers, the following condition matches rows with employees listed in both sources:

The Developer tool validates datatypes in a simple condition. Both ports in the condition must have the same datatype. If you need to use two ports in the condition with non-matching datatypes, convert the datatypes so they match.

You can configure a list of join conditions in a simple condition. When you configure multiple join conditions, all the conditions must be true to make the join.

For example, you might configure the following statements in a simple condition:

If you view the same statements as an Advanced Condition, the join condition appears as the following expression:

An advanced condition can include any expression that evaluates to a Boolean or numeric value. An advanced condition can include any of the following operators: =, !=, >, >=, <, <=.

You can enter a constant for the join condition. The numeric equivalent of FALSE is zero (0). Any non zero value is the equivalent of TRUE. For example, the transformation contains a port named NUMBER_OF_UNITS with a numeric data type. You configure a filter condition to return FALSE if the value of NUMBER_OF_UNITS equals zero. Otherwise, the condition returns TRUE.

To enter an expression in the join condition, choose the Advanced condition type on the Join tab. Use the Expression Editor to include ports, parameters, expressions, port selectors, and operators in the condition. You can use generated ports. You can enter a single port in the Expression Editor if the port type is numeric. However, you cannot enter one port selector as an expression.

For example, you want to join sources by matching an employee's full name. The master source includes a FirstName and a LastName port. The detail source includes a FullName port. Define the following condition to concatenate the master ports and match the full name in both sources:

For example, the Joiner transformation has dynamic ports. You might need to compare multiple generated ports in the join condition.

The following image shows the fields in the port selector for the master group:

The Master_Cust_Selector contains has the CustomerID and FullName ports.

The following image shows the fields in the port selector for the detail group:

The Detail_Cust_Selector contains has the CustomerNo and CustFullName ports. These ports have the prefix Cust.

Create the following simple join condition:

The join condition compares each port in the Master_Cust_Selector to the Detail_Cust_Selector. The join condition is: CustomerID = CustomerNo AND FullName = CustFullName.

Each port selector must contain the same number of ports. The ports must be the same type.

A dynamic port can contain one of more generated ports. If the join condition contains dynamic ports, the number of master ports must be the same as the number of detail ports.

For example a dynamic port A has 2 generated ports:

Dynamic port B also has 2 generated ports:

The following join condition is valid:

DynamicPortA = DynamicPortB

The join condition expands to the following expression:

You can reference a port selector and a dynamic port in a join condition if the port selector contains the same number of ports as the dynamic port.

To use a parameter for the join condition, select the parameter condition type on the Join tab.

The following image shows where to select the parameter condition type:

You can browse for an existing parameter or you can create a parameter. To create a parameter, click New and define the parameter. Create the expression in the expression editor.