Informatica Problems With Solutions - Part 1

1. In this problem we will see how to implement the not equal operator, greater than, greater than or equal to, less than and less than or equal to operators when joining two tables in informatica.
Consider the below sales table as an example?
Table name: Sales

product, prod_quantity, price , Year
A         , 10                 , 100  , 2010
B         , 15                 , 150  , 2010
A         , 8                   , 80    , 2011
B         , 26                 , 260  , 2011

Now the problem is to identify the products whose sales is less than in the current year (In this example: 2011) when compared to the last year.
Here in this example, Product A sold less in 2011 when compared with the sales in 2010.
This problem can be easily implemented with the help of SQL query as shown below

SELECT  cy.*
FROM    SALES cy,
SALES py
WHERE   cy.product = py.product
AND        cy.year=2011
AND        py.year=2010
AND       cy.prod_quantity < py.prod_quantity;

In informatica, you can specify only equal to condition in joiner. Now we will see how to implement this problem using informatica.
Solution:
STEP1: Connect two source qualifier transformations to the source definition. Call the first source qualifier transformation as sq_cy  (cy means current year) and the other as sq_py  (py means previous year).
STEP2: In the sq_cy source qualifier transformation, specify the source filter as price=2011. In the sq_py, specify the source filter as price=2010
STEP3: Now connect these two source qualifier transformations to joiner transformation and make sq_cy as master, sq_py as detail. In the join condition, select the product port from master and detail.
STEP4: Now connect all the master ports and only the prod_quantity port from detail to the filter transformation. In the filter transformation specify the filter condition as prod_quantity < prod_quantity1. Here pord_quantity port is from master port and prod_quantity1 is from detail port.
STEP4: Connect all the ports except the prod_quantity1 of filter transformation to the target definition.
2. How to implement the not exists operator in informatica which is available in database?
Solution:
Implementing the Not Exists operator is very easy in informatica. For example, we want to get only the records which are available in table A and not in table B. For this use a joiner transformation with A as master and B as detail. Specify the join condition and in the join type, select detail outer join. This will get all the records from A table and only the matching records from B table.
Connect the joiner to a filter transformation and specify the filter condition as B_port is NULL. This will give the records which are in A and not in B. Then connect the filter to the target definition.