Database SQL Minus Set Operator in Informatica

How to Implement the mapping logic for the database minus set operator in Informatica?

The SQL Minus set operator returns the distinct rows from the first select statement which are not in second select statement. Lets consider the employees table as an example:

Table Name: EMP_A

emp_id, name
------------
1,  Scott
1,  Scott
2,  Matt
2,  Matt
3,  Chris

Table Name: EMP_B

emp_id, name
------------
2,  Matt
2,  Matt
3,  Chris

The result of EMP_A minus EMP_B is shown below:

select * from EMP_A
MinuS
select * from EMP_B


emp_id, name
------------
1, scott

Follow the below steps for implementing the mapping logic for this. 1. Create the source qualifier transformations for both EMP_A and EMP_B by dragging the sources from left side navigation into the mapping workspace.

2. Join these two sources using the joiner transformation. In the joiner transformation, treat the EMP_A as detail and EMP_B as master. Go to the properties tab of joiner and specify the join type as master outer join. Go to the condition tab and specify the join condition as shown below:

EMP_A.emp_id = EMP_B.id
EMP_A.name = EMP_B.name

3. Connect the joiner transformation to the filter transformation and specify the filter condition as ISNULL(EMP_B.emp_id) and ISNULL(EMP_B.name)

4. Connect the filter to a sorter transformation. Check the distinct option in the properties tab of sorter transformation.

5. connect the sorter transformation to the target and save the mapping.

If you have any problem in Implementing the logic for minus operator, please comment here.