Urgent...Pls help

[rubysaha]

I have a source table which stores the data in the following format

CASE_ID DRUG_DE PT_DE
---------------------------------------------------
1 ab;abcd;de;fj;sdgh hj[12];cf[34];sdv[78]

the length of DRUG_DE and PT_DE is variable

And I have two target tables which will store data as follows

CASE_ID DRUG_DE
--------------------------
1 ab
1 abcd
1 de
1 fj
1 sdgh


CASE_ID PT_CODE PT_DE
------------------------------------
1 12 hj
1 34 cf
1 78 sdf

How can i achieve the above requirement?

Thanks in advance.

 

[tkrish]

Use Advanced External Procedure transformation and have a perl program that does all the parsing for you.

Search for "Perl output strings" in Help topics and you'll find a nice article describing all the steps.

tkrish.