Router Transformation in ODI
Router Transformation in ODI:
Here is the post you were looking for. I had to spare few time as I was getting so many emails on this. You can refer this post if you are interested to use multiple interface for accomplishing the same. If you are thinking of a single interface then here is the solution.
Source Table :EMP
Target table: EMP_TGT1,EMP_TGT2,EMP_TGT3
KM: IKM Incremental Update
Please create below two tables
create table TABLE_NAME (
create table CONDITION_NAME(
Now reverse EMP,EMP_TGT1. Dont bother about that #GET_T as given in below image.We will do that part at the end.
Now create 5 odi variables CHECK_C,CHECK_T,GET_C,GET_T,LOOP
Create the interface with source as EMP and target as EMP_TGT1 and then execute it.
Once above interface will run successfully then create a filter as given in below image.
Now create a new ODI procedure with below steps.
Create a new option CONDITION_NAME in the procedure to provide the condition separated by commas.
Just right click on procedure and click on New Option.
Well now save it and come to the next step of the procedure that is insert conditions and put below codes inside it.
SELECT REGEXP_COUNT(COND ,’,’ ) INTO CNT FROM DUAL;
while CNT!=0 loop
INSERT INTO CONDITION_NAME VALUES(CSEQ.NEXTVAL,REGEXP_SUBSTR ( COND, ‘[^,]+’, 1, CNT ));
INSERT INTO TABLE_NAME (TID,TNAME)
SELECT TSEQ.NEXTVAL,TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE ‘EMP_TGT%’
Next open the target datastore and modify the resource name to #GET_T. This variable will read table names dynamically.You can say this is the reverse of loading multiple files to one target table where source datastore will keep this variable.
Now arrange all the object as given in below diagram.Remember 1st LOOP variable assign value=1 and in next LOOP variable increment by 1. Similarly CHECK_T variable should be greater than 0 in evaluate mode.
YOu are done. Lets execute the package.Here is the session details from operator.