March 31, 2013 at 6:13 pm
I Have a fixed width flat file and that needs to be loaded into multiple oracle tables(one row need to be splitted into multiple rows)
the numbers which are on top of each column is there size,
and my desired output should look like shown below.
Flatfile data(fixed width):
3 6 3 11 3 10 3 10 3
ID NAME AGE CTY1 ST1 CTY2 ST2 CTY3 ST3
200JOHN 46 LOSANGELES CA HOUSTON TX CHARLOTTE NC
201TIMBER54 PHOENIX AZ CHICAGO IL
202DAVID 32 ATLANTA GA PORTLAND AZ
the occurrence may vary.. it can grow upto 20-30
DESIRED OUTPUT:
TABLE1
ID NAME AGE
200JOHN 46
201TIMBER54
202DAVID 32
TABLE2
ID SEQ CTY ST
200 1 LOSANGELES CA
200 2 HOUSTON TX
200 3 CHARLOTTE NC
201 1 PHOENIX AZ
201 2 CHICAGO IL
202 1 ATLANTA GA
202 2 PORTLAND AZ
can some one help me out
Thanks
March 31, 2013 at 6:38 pm
Once you have the flat file source set up correctly, you could multicast and then send to two OLEDB destinations mapping the fields you want for each.
March 31, 2013 at 6:43 pm
Thanks for the response
If its a straight move to two tables i can do wht u mentioned.
But the data into table2 need to be split for various cities and states for each ID
March 31, 2013 at 6:47 pm
Sorry, I read too fast and missed that part.
For the stream that will go into table2, you can use unpivot, specifying ID as passthrough, Seq as PivotKey column, City1/ST1 with pivot key value 1; City2/St2 with pivot key value 2, etc. and destination columns of City/St.
That would give you a dataset in the format you are wanting. Then you could just map that to your destination.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply