Joining Source Tables

Data Integrator allows you to join data from multiple sources before extraction. You can create join views by creating a join condition that joins source tables.

For optimal performance, join the most unique columns in the first join and the least unique columns in a second join.

To Join Source Tables

1. Open the ETL collaboration in the ETL Collaboration Editor.

2. In the ETL Collaboration Editor toolbar, click Create New Join.

   The Create New Join View dialog box appears.

   

3. Under Available Tables, select the tables you want to join and then click the right arrow.

   The tables move to the Selected Tables column, and the join is represented graphically in the Preview panel.

   

4. In the Preview panel, click the down arrow in the join condition and select the type of join to use from the following options.

   • Inner – Use this if all tables to be joined contain the same column.

   • Left Outer – Use this if the results should always include the records from the left table in the join clause.

   • Right Outer – Use this if the results should always include the records from the right table in the join clause.

   • Full Outer – Use this if the results should always include the records from both the right and left tables in the join clause.

5. By default, all columns are selected for the join condition. To deselect any columns, click the Select Columns tab and then deselect any columns you do not want to include in the join.

   

6. To define the join condition, click inside the join box. On the Edit Join Condition dialog box, do the following:

   1. To view the SQL code while you create the join condition, click the SQL Code tab. To view the join condition graphically, click the Graphical tab.

   2. Define the join condition by dragging column names from the list in the left panel. Join the column names by dragging operators from the toolbar.

      In the example below (shown in both source code and graphical views), PERSONID was dragged from the SBYN_PHONE table first. Then the equals operator, located in the Comparison Operators menu, was dragged next to PERSONID. To complete the condition, PERSONID was dragged from the SBYN_PERSON table.

      

   3. Define as many conditions as needed.

   4. When you are done defining conditions, click OK.

7. Click OK on the Edit Join View dialog box.

To Join Source Tables During Mapping

If two source tables are already joined and have columns that are mapped to a target table, you can add another source table to the join by mapping a column in that table to the target table. For example, if source tables S1 and S2 are joined and mapped to target table T1, you can add source table S3 to the join by mapping a column from S3 to T1.

1. Map a columns from the source target you want to add to the join to the target table that is already mapped to the joined tables.

   A dialog box appears asking whether you want to add the new table to the join.

   

2. Click OK to add the new source table to the existing join view.

   The Edit Join View dialog box appears.

   

3. Click in the second join box in the Preview panel.

4. Define the join conditions by dragging columns and operators onto the canvas.