Pages

Tuesday, March 19, 2013

A source table contains emp_name and salary columns. Develop an Informatica mapping to load all records with 5th highest salary into the target table.


Solution:
The mapping will contain following transformations after the Source Qualifier Transformation:
1. Sorter : It will contain 2 ports – emp_name and salary. The property ‘Direction’ will be selected as ‘Descending’ on key ‘Salary’
2. Expression transformation: It will 6 ports as follows -
a> emp_name : It will be an I/O port directly connected from previous sorter transformation
b> salary_prev : It will be a variable type port. Give any vriable name e.g val in its Expression column
c> salary : It will be an I/O port directly connected from previous transformation
d> val : It will be a variable port. The expression column of this port will contain ‘salary’
e> rank: It will be a variable type port. The expression column will contain decode
(salary,salary_prev,rank,rank+1)
f> rank_o : It will be an output port containg the value of ‘rank’.

3. Filter Transformation : It will have 2 I/O ports emp_name and salary with a filter condition rank_o = 5
The ports emp_name and salary from Filter Transformation will be connected to target

No comments:

Post a Comment