How to Join multiple tables without Joiner Transformation

How to Join multiple tables without Joiner Transformation

Lets see one scenario, how to join two tables without using Joiner Transformation. This can be achieved by just using Source Qualifier – ‘User Defined Join’ property. Here we are going to join Two tables (EMPLOYEE & JOBS) and create a target table with columns from both the tables.

Join multiple tables without Joiner Transformation
First Source Table – EMPLOYEE
Join multiple tables without Joiner Transformation
Second Source Table – JOBS
Join multiple tables without Joiner Transformation
Created a mapping and dragged both the source, then removed one Source qualifier and connected the other table ports as well to the first Source qualifier, We need only one SQ here since we are going to build one SQL query (Join Query) to fetch the source records.
Join multiple tables without Joiner Transformation
Edit the SQ property and give the User Defined join conditions. Here we are matching the JOB_ID from both the EMPLOYEE & JOBS tables.
Join multiple tables without Joiner Transformation
Ran the session for this mapping and here is the Session log. Here you can see the SQL query used by the Source qualifier to join both the tables.
Join multiple tables without Joiner Transformation
Here is the target table result. We have columns from both the source tables.

We are done. Joined two tables without joiner transformation. If you have more number of tables to join, always prefer to go with Joiner transformation which will support cache and will give performance advantages over Source Qualifier.

Leave a Reply

Your email address will not be published. Required fields are marked *