Source Qualifier – Full Outer Join

by kvt - Comment is Closed

Full Outer Join using Source Qualifier – ‘SQL Query’

Integration service support only Left & Right outer joins. We cannot perform Full outer join using ‘User Defined Join’ properties in SQ, you will get a database error. This can be done by using overriding default ‘SQL query‘ in SQ.

Full Outer Join Query

SELECT EMP_JOB1.JOB_ID, EMP_JOB1.JOB_TITLE, EMP_JOB1.MIN_SALARY, EMP_JOB2.MAX_SALARY
FROM
emp_job1 FULL OUTER join emp_job2
on emp_job1.job_id=emp_job2.job_id

Source Qualifier - Full Outer Join
Created 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 Full Outer Join
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.
Source Qualifier Full Outer Join
Total 7 records got read, processed and loaded to target. All matched records and unmatched records from both the tables.
Source Qualifier Full Outer Join
See the source Qualifier SQL query in the session log. Integration uses the same query which we given in the SQL editor.
Source Qualifier Full Outer Join
Output Target Table – Matched records (3) from both the tables, unmatched records from left (2) and unmatched records from right (2) table got loaded to target.

Related Posts: