Source Qualifier – User Defined Join – Normal Join

How to join two tables – Source Qualifier – User Defined Join

** Source Qualifier Transformation is the most efficient way to join two tables in the same Database **

There are two options available in SQ to join multiple tables, One using ‘User Defined join’property and the other using ‘SQL Query’. We can create a join override to do a Normal, Left & Outer join using ‘User Defined join’ but will get a database error if you try to do Full outer join.  Integration service does not support Full outer join. For that we can override the SQL query and get it done.

User-Defined Join
Create a join override. The Integration Service appends the join override to the WHERE or FROM clause of the default query.

SQL Query
Override the Default query with Join SQL query.

Normal Join 

The integration service process only matched records from both the tables and discard those that do not match.

Source Qualifier - User Defined Join - Normal Join (Inner)
JOB1 – This is our First table
Source Qualifier - User Defined Join - Normal Join (Inner)
JOB2 – This is our second table. We are going to join these two table and load to target using SQ.
Source Qualifier - User Defined Join - Normal Join (Inner)
Create a mapping with Source Qualifier Transformation, We need only 1 SQ instance here with the ports from both the tables since we are writing a single query to read from the source.
Source Qualifier - User Defined Join - Normal Join (Inner)
Edit the SQ property and give the below SQL expression in ‘User Defined Join’ editor. Just note the Join syntax, enclose the entire join statement in braces or else will get DB error.

We can also give the SQL expression like below, which would be same as INNER JOIN and give you the same results. emp_job1.job_id = emp_job2.job_id.

Source Qualifier - User Defined Join - Normal Join (Inner)
We can also see the same ‘User Defined Join’ Expression in session level and can be override from here if needed.
Source Qualifier - User Defined Join - Normal Join (Inner)
Create a workflow & session and run the job, Lets review the session log. Only matched records (3) from source got read, processed and loaded to target.
Source Qualifier - User Defined Join - Normal Join (Inner)
See the Source Qualifier SQL query used in the mapping, since we used the ‘User Defined Join’ property, Integration service override the default SQL query and changed to a join query. 

 

Source Qualifier - User Defined Join - Normal Join (Inner)
Output Target Table – We have only those three matched records loaded to target. Job ID, Title & Min Salary from first table and Max Salary from second table.

We are done with Normal Join – Lets see the other option by overriding source qualifier SQL Query.

How to join two tables – Source Qualifier – SQL Query

How to join two tables – Source Qualifier - SQL Query
Edit the SQ property and give the complete SQL query to read from the source. Integration service will use this query instead of default to get the data.
How to join two tables – Source Qualifier - SQL Query
Only matched records (3) from both the source tables got read, processed and loaded to target.
How to join two tables – Source Qualifier - SQL Query
See the source Qualifier SQL query in the session log. Integration uses the same query which we given in the SQL editor.
Source Qualifier - User Defined Join - Normal Join (Inner)
Output Target Table – We have only those same three matched records loaded to target. Same result as above.

We are done with Normal Join – Lets see Left Outer join in the next session.

You May Also Like

About the Author: kvt

Leave a Reply

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