Monday 5 May 2014

How to load the 5 highest salaried employee into the target without using rank transformation?



Solution:

 Here in my example I am taking the source as emp table from oracle scott schema.

 TABLE EMP

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 12/17/1980 0:00 800 20
7499 ALLEN SALESMAN 7698 2/20/1981 0:00 1600 300 30
7521 WARD SALESMAN 7698 2/22/1981 0:00 1250 500 30
7566 JONES MANAGER 7839 4/2/1981 0:00 2975 20
7654 MARTIN SALESMAN 7698 9/28/1981 0:00 1250 1400 30
7698 BLAKE MANAGER 7839 5/1/1981 0:00 2850 30
7782 CLARK MANAGER 7839 6/9/1981 0:00 2450 10
7788 SCOTT ANALYST 7566 12/9/1982 0:00 3000 20
7839 KING PRESIDENT 11/17/1981 0:00 5000 10
7844 TURNER SALESMAN 7698 9/8/1981 0:00 1500 30
7876 ADAMS CLERK 7788 1/12/1983 0:00 1100 20
7900 JAMES CLERK 7698 12/3/1981 0:00 950 30
7902 FORD ANALYST 7566 12/3/1981 0:00 3000 20
7934 MILLER CLERK 7782 1/23/1982 0:00 1300 10

TARGET
RANK EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 7839 KING PRESIDENT 11/17/1981 5000 10
2 7788 SCOTT ANALYST 7566 12/9/1982 3000 20
2 7902 FORD ANALYST 7566 12/3/1981 3000 20
3 7566 JONES MANAGER 7839 4/2/1981 2975 20
4 7698 BLAKE MANAGER 7839 5/1/1981 2850 30
5 7782 CLARK MANAGER 7839 6/9/1981 2450 10

Algorithm
  1. Take the source
  2. Take a sorter and sort on SAL by descending order as

  3. Take an expression transformation
  4. Create the following ports with the formula
       check_v     = iif(SAL   !=   prev_sal_v,check_v +1, check_v)
       prev_sal_v  = SAL
       flag_o        = check_v
 
   
   5. Take a filter transformation
   6. Drag all the ports from the expression transformation into the filter transformation and give flag_o<=5  in the filter condition.
   7. Pass the ports into the target.

That's all.
The final mapping will look like


What about the 5 lowest salaried employees without using rank transformation?

No comments:

Post a Comment