Difference between Case and Decode

Decode	Case
It can use only scalar values	It can use logical operator, predicates, and searchable sub queries.
It can be used in SQL statement	It can be used in PLSQL
Decode is proprietary to Oracle	It is compile with ANSI SQL
Decode is a function	It is a statement

Difference between NVL and NVL2

  NVL(exp1,exp2)
  if the exp1 is NULL then it will return exp2 otherwise it will return exp1.

  NVL2(exp1,exp2,exp3)
  if the exp1 is NULL then it will return exp3 otherwise exp2.

  here is another function which is used to check for null values

  COALESCE(exp1,exp2,expr3....)
  it will return first OT NULL expression or value.

Transformation which use cache

Transformation	Index Cache	Data Cache	Directory
Aggrigator	Group ports	Aggrigated data	cache ($PMCacheDir}
Rank	Group infromation	row data	cache
Lookup	it holds the Lookup columns	It holds the return columns.	cache
Joiner	It it holds the join column from the master table.	It holds the master table rows.	cache
Sorter.			temp Directory ($PMTempDir)

Convert column into row without using Normalizer Transformation

Source is

ID	YEAR1	YEAR2	YEAR3
1	200	300	400
2	500	600	700

Output 

ID	YEAR
1	200
1	300
1	400
2	500
2	600
2	700

Achieve the result without using Normalizer Transformation. 

Solution:

1. Bring the source in to the mapping designer.

2. Create three expression transformation.

3. Drag ID and YEAR1 ports from the source qualifier into the first expression.

4. Drag ID and YEAR2 ports from the source qualifier into the second expression

5. Drag ID and YEAR3 ports from the source qualifier into the third expression.

6. Take a UNION transformation and create three ports.

7. Drag ports from the first expression transformation into the first group of union transformation.

8. Drag ports from the second expression transformation into the second group of union transformation.

9.  Drag ports from the third expression transformation into the third group of union transformation.

10. Take a sorter transformation and sort on id.

11. Now connect to the target.

That's all and final mapping is

Difference between IF and DECODE

DECODE can be used in SQL statement
IF can't be used in SQL statement.

Find the rank of the employee within department based on salary

My source is

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

My target would be

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	DEPTNO	rank
7839	KING	PRESIDENT	-	29907	5000	10	1
7782	CLARK	MANAGER	7839	29746	2450	10	2
7934	MILLER	CLERK	7782	29974	1300	10	3
7788	SCOTT	ANALYST	7566	30294	3000	20	1
7902	FORD	ANALYST	7566	29923	3000	20	1
7566	JONES	MANAGER	7839	29678	2975	20	2
7876	ADAMS	CLERK	7788	30328	1100	20	3
7369	SMITH	CLERK	7902	29572	800	20	4
7698	BLAKE	MANAGER	7839	29707	2850	30	1
7499	ALLEN	SALESMAN	7698	29637	1600	30	2
7844	TURNER	SALESMAN	7698	29837	1500	30	3
7521	WARD	SALESMAN	7698	29639	1250	30	4
7654	MARTIN	SALESMAN	7698	29857	1250	30	4
7900	JAMES	CLERK	7698	29923	950	30	5

Solution:

    1. Bring the source into the mapping designer.
    2. Take a sorter transformation. ( If you do sorting in the source qualifier will be the best one).
    3. Apply sorting logic as SELECT * FROM EMP ORDER BY DEPTNO ASC, SAL DESC
   

Note: If you keep DEPTNO port after the SAL port then the sql query will become SELECT * FROM EMP ORDER BY SAL DESC, DEPTNO ASC. That will give wrong result. So make sure you keep the DEPTNO port before the SAL port.
 
    4. Take an expression transformation and create four ports as
        rank_v = iif(DEPTNO = prev_deptno_v,iif(SAL != prev_sal_v,rank_v + 1, rank_v) , 1 )
        prev_deptno_v = DEPTNO
        prev_sal_v  = SAL
        rank_o = rank_v
     

      5. Pass the ports into the target.

      That's all and whole final mapping is