Auto

Sep 21 2017

Source Qualifier – User Defined Join – Left Outer Join – Explore Informatica, left outer join db.#Left #outer #join #db

#

Explore Informatica

Source Qualifier User Defined Join Left Outer 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.

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

Override the Default query with Join SQL query.

Integration Service returns all rows for the table to the left of the join syntax and the rows from both tables that meet the join condition. In other words it returns all rows from the left table (Table1), with the matching rows in the right table (Table2). The result is NULL in the right side when there is no match.

Left outer join db JOB1 This is our First table (Left) Left outer join db JOB2 This is our second table (Right). We are going to join these two tables and load to target using SQ Left outer join db 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. Left outer join db 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.

Left outer join db Created a workflow session for the mapping and ran the job, Let s review the session log. all rows from the left table (Table1), with the matching rows in the right table got read, processed and loaded to target. Left outer join db 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. Left outer join db Output Target Table – All records from left table (Matched-3 + Unmatched-2) got loaded to target. Job ID, Title Min Salary from first table and Max Salary from second table. Since we dont have any max_salary for job_id 4 and 5 its showing as NULL.

Left Outer Join using Source Qualifier SQL Query

The same result can be achieved by overriding the default SQL query.

Left outer join db 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.

SELECT EMP_JOB1.JOB_ID, EMP_JOB1.JOB_TITLE, EMP_JOB1.MIN_SALARY, EMP_JOB2.MAX_SALARY

emp_job1 LEFT OUTER join emp_job2

Left outer join db Same result as above, Only 5 records loaded to target (All records from Left Table) Left outer join db See the source Qualifier SQL query in the session log. Integration uses the same query which we given in the SQL editor. Left outer join db Output Target Table – We have only those same 5 records, All records from left table (Matched-3 + Unmatched-2)

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

Written by CREDIT


Leave a Reply

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