Thursday, November 15, 2012


I have a scenario where i need to load a table called "name_address" first for storing all the claimant name with a generation of a number (using given formula).

This number will be used in other main tables like claim table , transaction .. etc

Can u please help me whether constraint based loading will be useful or i need to specify target load plan.


Col_1    Col_2       
D1       A1          
D1       A2          
D2       A3         
D2       A4           

D2       A5           
D3       A6           
D3       A7 

Col_1    Col_2   
D1     A1-A2 
D2    A3-A4-A5 
D3     A6-A7

Answer :

The Following can be obtained by using a Sorter transformation , Expression transformation and a aggregator transformation.

Let me show the mapping now.,
This shows the mapping for the above scenario.
Now lets see them in detail
1) Usual Sorter on Col1
2) Expression t/f like below
3) Aggregator t/f as below

Create different flatfiles as output

We have a scenario in our project where i have to create different flatfiles as output based on the company number

The issue is 
1) we run this workflow once in every month
2) New company numbers and corresponding records may be added anytime during the month ., So each time wen a company is added we have to create a flatfile for that also
3) The name of the flatfile should be "Loss history report for" + company number
4) One more thing is every month wen the workflow runs , the flatfiles have to be places in that month folder . eg : In january the files shud be placed in Jan2012 folder.
The folders will be already present.,
5) Is it possible to create the folders also inside target every month ?

Does anyone have answer for this?

Wednesday, November 14, 2012

Java Transformation scenario2

Here i will show the mapping for scenario where we need the input records from emp table to be inserted into target thrice.


One more requirement is to change ename to "java" || job

They both are implemented the below way :

check out this link for other java scenario

Java Transformation

Let me show the Simple mapping involving Java transformation

Here the source is emp table and the target is rangasp table in oracle.

The requirement here is to change all the employee name to "java".
The java code has been written in such a way that the ename of all records will be changed to "java"

The left side contains all the input and output ports.
Generaterow() is used to generate a row for each records.

The output is something like this :

eno ename
721 java
678 java
899 java

Please do comment and ask if you have any doubts!! thankyou

Tuesday, November 13, 2012

Scenario 4

Input :

empno    ename    job
7219     Sri      CLERK
7782     Uma    MANAGER
7839     Ragu   PRESIDENT
7934     Sendil   CLERK

Output :

Repeation of the rows three times and also change the ename as "Srivatsan"
empno    ename    job
7219     Srivatsan CLERK
7219     Srivatsan CLERK
7219     Srivatsan CLERK
7782     Srivatsan MANAGER
7782     Srivatsan MANAGER
7782     Srivatsan MANAGER
7839     Srivatsan PRESIDENT
7839     Srivatsan PRESIDENT
7839     Srivatsan PRESIDENT
7934     Srivatsan CLERK
7934     Srivatsan CLERK
7934     Srivatsan CLERK

Post your mapping , one lucky winner will be selected!!

Transaction control Transformation

Lets discuss about transaction control transformation in this video :

I will show you a scenario using transaction control statement and explain you wen it has to be used.

Here we can see three components

1) expression trans

2) transaction ctrl t/f

3) target

The source for us is the emp table of scott schema

the requirement is to split the source rows into different flatfiles based on the dept number.

that is:

dept no : 10 , 20 ,30 need to splited and saved in seperate files

We can ask a question like "why cant this be done easily by a router"

yep a "good ques : the answer is "wen the dept no changes dynamically how we will be able to get the router groups to change??

So this is how this scenario is handled..

here we have filed where we are checking this condn : IIF(v_DEPTNO_2 != DEPTNO, 0, 1)

if a dept number comes for the first time then this will be zero and our transaction control t/f will create a flatfile with corresp rows.

filename is a field used to provide name of flatfile which need to be generated dynamically.

Based on the column where we gave condn the tc t/f does the following commiting activity

here wenever a new dept number comes a commit is been done and a flatfile is created and one imp note is "the input has to be sorted initially"

In this way the files are created on each dept numbers

Thankyou , please comment!!

Normalizer transformation scenario

How can we do this:
StudentName Subject1 Marks1 Subject2 Marks2
Sam Maths 100 English 80
John Maths 85 English 75

to the target:

StudentName Subject Marks
Sam Maths 100
Sam English 80
John Maths 85
John English 75

Answer :

In this way we can get the above output