ROW SPLITTER PROBLEM

ianm · Post by **ianm** » Mon Jul 11, 2005 4:39 am

I've found topics on rowsplitter but they don't give me an answer to my particuler problem.

I have an Oracle table that contains columns :-
Orderref varchar(6)
Status varchar(2)
Submission_Tstamp Timetamp (19)
Order_Data varchar(2040)

Order_Data contains information about options the customer selected when placing the order. The option codes appear at 12 character intervals.

e.g.
code1.......code2.......code3.......code4 e.t.c.

I am trying to break the Order_Data column into multiple rows so I end up with :-
e.g.
OrderRef Status Submission_Tstamp Option
====== ==== ============= ====
000001 01 2004-07-11 00:00:00 Code1
000001 01 2004-07-11 00:00:00 Code2
000001 01 2004-07-11 00:00:00 Code3
000001 01 2004-07-11 00:00:00 Code4

I am using a job :-
OCI ==> Row Splitter ==> seq File

If on the output I specify the single column Option varchar(12) as fixed width(12) it works o.k.
Option
====
Code1
Code2
Code3
Code4

but as soon as I try to add in the other columns I want passed through I start to have problems. Either "Required Column Missing" which they aren't as they are specified in the OCI source stage.
If I turn of the Missing column message then it tries to split the Order_Data column over the extra columns
e.g.
OrderRef Status Submission_Tstamp Option
====== ==== ============= ====
Row1 Code1 Co de2Code3Code 4000001

or something close to that.

Is there some setting that I am missing?
I've tried swapping the order of the columns around to no avail.

Can anybody help ?

ianm

ray.wurlod · Post by **ray.wurlod** » Mon Jul 11, 2005 6:36 am

Looks like you've picked the wrong stage type. Investigate the Pivot stage for this particular task.

ianm · Post by **ianm** » Tue Jul 12, 2005 1:14 am

Thanks Ray.
I did use the pivot stage in the end although it was a tedious way round, having to split the data out into columns first then combining them back together again.
Leaves me wondering what the point of the row splitter stage is though ?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Jul 12, 2005 4:44 am

Did you try passing the column to break alone?

ray.wurlod · Post by **ray.wurlod** » Tue Jul 12, 2005 3:20 pm

Row Splitter can do the breakout into separate columns.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Jul 12, 2005 3:38 pm

But starting from step 1 will be a good guide to identify where the columns disappear.

talk2shaanc · Post by **talk2shaanc** » Tue Jul 12, 2005 11:20 pm

row spliter splits A row, NOT spilts into rows.
Taking a simple example. if have column Name with value James Anderson
Then using row splitter you can have two column First name=James and Last Name=Anderson.

The row splitter stage is similar to the server sequential file stage. The difference is that, while the sequential file stage reads from a file, the row splitter stage reads from a link.

For your requirement, I would suggest pivot or in the transformer have one column as output, to which you give derivation code1:char(13):char(10):code2:char(13):char(10):code3:char(13):char(10):code4....and so on.