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 -

• EMP_NAME: It will be an I/O port directly connected from previous sorter transformation
• SALARY_PREV: It will be a variable type port. Give any variable name e.g Val in its Expression column
• SALARY: It will be an I/O port directly connected from previous transformation
• VAL: It will be a variable port. The expression column of this port will contain ‘salary’
• RANK: It will be a variable type port. The expression column will contain decode (salary,salary_prev,rank,rank+1)
• RANK_O: It will be an output port contain 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.