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