Previous Scenarios
Scenario: 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
Pages
▼
Wednesday, March 3, 2010
Divisions
A table contains certain columns. One of the columns is Division. The user enters a division name through a prompt. Write a query to satisfy below scenario:
If the value entered in prompt is 'a' or 'b', then all records should be displayed else, the records pertaining to that particular division should be displayed.
Solution:
The query would be written as follows:
select * from my_table
where
division = &division
OR
'^' = decode(&division,'a','^','b','^',&division)
Here any special character (like '^' in above query) can be choosed to give all results in case division value entered from prompt is either 'a' or 'b'.
If the value entered in prompt is 'a' or 'b', then all records should be displayed else, the records pertaining to that particular division should be displayed.
Solution:
The query would be written as follows:
select * from my_table
where
division = &division
OR
'^' = decode(&division,'a','^','b','^',&division)
Here any special character (like '^' in above query) can be choosed to give all results in case division value entered from prompt is either 'a' or 'b'.