Informatica Scenarios

                                                                                                 

1. Getting first and last record from the table.

a. Take SQ and connect it to 2 rank transformations.

b. Connect 1 sequence generator for the both rank transformations.

c. Connect the NEXTVAL for the both Rank transformations.

d. In the both Rank transformations give no of ranks 1.

e. Use Rank Port also NEXTVAL.

f. And Ranks need to be generate one is TOP and another one is BOTTOM and no of ranks is 1 for both rank transformations.

g. Finally connect it to the target use target instance.

2. Sending first half records into with Query Overwrite option.

a. Take SQ and connect it to Target directly.

b. Open the SQ overwrites the query.

c. Select * from EMP where rownum<= (select count (*)/2 from EMP).

d. Connect it to Target.

3. Sending Second half records into with Query Overwrite option.

a. Take SQ and connect it to Target directly.

b. Open the SQ overwrites the query.

c. Select * from EMP minus Select * from EMP where rownum<= (select count (*)/2 From EMP)).

d. Connect it to Target.

4. How to send alternate records to the target. Otherwise how to send odd and even records to each targets separately.

a. Take SQ and connect it to EXP transformation.

b. Connect sequence generator also for the EXP transformation.

c. Connect the NEXTVAL for the EXP transformation.

d. Create the new ports and write the expressions in the EXP transformation shown below.

e. Also Odd condition = MOD(NEXTVAL,2)=1, Even condition = MOD(NEXTVAL,2)=0.
f. Connect it RTR transformation and create two groups for that one for EVEN and Another for ODD.

g. Create two groups in RTR like EVEN, ODD.

h. For EVEN filter condition odd=0, For ODD filter condition odd=1.

i. Connect it to two different targets.

5. How to separate the original records from source table to separate target table by using

rank transformation?

a. Take SQ and connect it to RNK transformation.

b. In the RNK ports tab select rank port for the COL1.

c. And make remaining two columns as group by.

d. Finally connect it to the TARGET.

6. How to get top 5 records to target without using Rank.

a. Take SQ and connect it to SORTER transformation.

b. Sort by the SAL in Descending order.

c. Connect the Sequence Generator and Sorter to the EXP transformation.

d. From the EXP to connect it to FLTR or RTR and give the condition NEXTVAL<=5.

e. Finally connect it to the TARGET.

7. How to split one column into different with new filed. I have one column with 24 hour time format i want it to

Convert to AM, PM.

a. I have the source data in three columns like Col1, Col2 and Col3.

b. Take SQ and connect it to ROUTER transformation.

c. Take one group and write the shown condition for suppurate 0-11 as 12 am to 11 am.

d. Remaining will be pm in default group.

e. Connect two groups to two different EXPRESSION transformations.

f. In first EXPRESSION TRANSFORMATION for AM, take the new port as variable port and give the condition shown as below in the picture.

g. In second EXPRESSION TRANSFORMATION for PM, take the new port as variable port and give the condition shown as below in the picture.

h. Finally connect the all ports to the target and its instance respectively.

@@For More Mail me on  vnktsh143@gmail.com