How to do Row Convention in DataStage?
Posted: Tue Jul 15, 2003 11:38 pm
Hey All,
Im new to DataStage tool. Right now Im using DataStage 5.2 Client in Windows 2000. And DataStage Server also in Windows 2000 and Version 5.2. And Im using ORAOCI8 to both Source and Target sides. Backend is Oracle 9i.
I have a table in oracle src_one (Source table) and the availble columns are,
Name Type
-----------------
E_ID NOT NULL NUMBER(3)
E_ONE NOT NULL CHAR(1)
E_TWO NOT NULL CHAR(1)
E_THREE CHAR(1)
And my lookup table is lkp_one (Source side)
Name Null? Type
--------- -------- ----------------------------
L_CD NOT NULL CHAR(1)
L_ID NOT NULL NUMBER
trgt_three (Target table) contains,
Name Null? Type
-------- -------- ----------------------------
E_ID NOT NULL NUMBER(3)
E_S_ID NOT NULL CHAR(1)
The Lookup data is
L_CD L_ID
A 10
B 20
C 30
D 40
Here I have table One Lookup ORAOCI8 and then I have to check each value of E_ONE is available in L_CD (Lookup column),If it there retrieve a Lookup column L_ID, then Insert with a record in Target table.Similarly, E_TWO also have to check with same LOOKUP, If value is there in Lookup table, again retrieve Lookup column and then insert with a new record in Target table, and so on. So the Target table should come like the following way.....
E_ID E_VAL
100 10
100 20
100 30
200 20
200 10
200 30
.
.
.
So E_ONE (Source table) value is there in L_CD (in Lookup) only then Insert a new record with L_ID in Target table side. Similar way to E_TWO and E_THREE columns also, to insert a new record. (Here I have taken only 3 columns to check, But I have 20 columns in Source side to check with same Lookup)
Can anybody help me out the above Convention? Whether I have to any Buil-in Routines here or to write any User diefined Routines? How to use this concept in DataStage? Pls give your suggestions or openions?
Thanx in advance.
Ravi
Im new to DataStage tool. Right now Im using DataStage 5.2 Client in Windows 2000. And DataStage Server also in Windows 2000 and Version 5.2. And Im using ORAOCI8 to both Source and Target sides. Backend is Oracle 9i.
I have a table in oracle src_one (Source table) and the availble columns are,
Name Type
-----------------
E_ID NOT NULL NUMBER(3)
E_ONE NOT NULL CHAR(1)
E_TWO NOT NULL CHAR(1)
E_THREE CHAR(1)
And my lookup table is lkp_one (Source side)
Name Null? Type
--------- -------- ----------------------------
L_CD NOT NULL CHAR(1)
L_ID NOT NULL NUMBER
trgt_three (Target table) contains,
Name Null? Type
-------- -------- ----------------------------
E_ID NOT NULL NUMBER(3)
E_S_ID NOT NULL CHAR(1)
The Lookup data is
L_CD L_ID
A 10
B 20
C 30
D 40
Here I have table One Lookup ORAOCI8 and then I have to check each value of E_ONE is available in L_CD (Lookup column),If it there retrieve a Lookup column L_ID, then Insert with a record in Target table.Similarly, E_TWO also have to check with same LOOKUP, If value is there in Lookup table, again retrieve Lookup column and then insert with a new record in Target table, and so on. So the Target table should come like the following way.....
E_ID E_VAL
100 10
100 20
100 30
200 20
200 10
200 30
.
.
.
So E_ONE (Source table) value is there in L_CD (in Lookup) only then Insert a new record with L_ID in Target table side. Similar way to E_TWO and E_THREE columns also, to insert a new record. (Here I have taken only 3 columns to check, But I have 20 columns in Source side to check with same Lookup)
Can anybody help me out the above Convention? Whether I have to any Buil-in Routines here or to write any User diefined Routines? How to use this concept in DataStage? Pls give your suggestions or openions?
Thanx in advance.
Ravi