test

Designing Data Integrator Projects

Using Table Aliases with Multiple Source Table Views

Sun Data Integrator only allows you to map a column in a source table to one column in a target table. If you need to map one source column to multiple target columns, you can use multiple instances of the same source table with different aliases. This topic gives a scenario and example for doing this.

The project has the following source tables: EMP_TBL and CODES_TBL. You can create a join view with these tables and you can drag another view of the CODES_TBL to the ETL Collaboration Editor canvas to create a third join. The third join is used in a code lookup.

The following table displays the sample data for the EMP_TBL source table:

Table 1–3 Employee Table

NAME

ID

JOB CODE

DEPT CODE

Dave 

1 

p 

D1 

Judy 

2 

c 

D2 

The following table displays the sample data for the CODES_TBL source table:

Table 1–4 Company Codes

CODE

VALUE

D1 

Human Resource 

D2 

Marketing 

P 

Permanent 

C 

Contractor 

The following figure shows the Collaboration and mapping with the correct data from a test run. The lookup loads the description for both jobs and departments from the CODES_TBL table. In this example, the table CODES_TBL is used twice in the join condition with aliases S2 and S3. In the join condition S2.Code is joined with S1.JOB_CODE and S3.Code is joined with S1.DEPT_CODE.

Figure shows the contents of a table in an ETL collaboration.

As you can see in the following figure, the first join view shows the condition S1.JOB_CODE = S2.CODE. This will load the job descriptions from the CODES_TBL to the target table column JOB.

Figure shows the Edit Join Condition window.

The following figure shows the second join view with the condition S1.DEPT_CODE = S3.CODE. This loads the department descriptions from the CODES_TBL to the target table column DEPT.

Figure shows the Edit Join Conditions window.