split a column value and create separate rows for each slit

[Jay]

Hi All,

Source is from a SQL Server.
Target is an Oracle table.

I have a requirement where the input is:
Computerid=1,Name="Solaris",Value="CPU1 1156MHz (sparcv9)
CPU2 1156Mhz
.
.
CPU32 1156MHz"

I have to ouput this to:
SurrKey, Computerid=1,Name="Solaris",Value="CPU1 1156MHz (sparcv9)"
SurrKey,Computerid=1,Name="Solaris",Value="CPU2 1156MHz (sparcv9)"
.
.
SurrKey, Computerid=1,Name="Solaris",Value="CPU32 1156MHz (sparcv9)"

Can someone please guide me in the ways this can be built?

I created Surrogate Keys in the transformer stage, after reading the max of surrkey from the Oracle table.Thats one part i have done.

Thanks in advance.
Jay

[chulett]

So... your example is of a single input record? And the last field, the internal values are separated by a newline? Also, to be anal, only your first value shows (sparcv9) at the end while all output examples show it... is that just a "typo" in your example? I'm assuming all internal values carry that as well.

[Jay]

Hi Craig,

Yes the last field is separated by newlines.

And yes its a typo. All the individual CPUs have a (sparcv9) at the end.

Thanks,
Jay

[chulett]

Any chance you are running the 8.5 release? If so, the transformer looping functionality would come in handy here. Otherwise, seems to me you'd need to split that last field into however many separate columns as it contains and then perform a horizontal (columns to rows) pivot.

[Nareshkotti]

Hi Jay,
By using Transformer stage along with a pivot stage we can do this.

Eg:
In the transformer take two more fileds
1. value1=value[1,up to 1st split u want]
2.value2=value[2nd split] -- substr functions.

Now in the pivot stage while deriving output columns assign value1,valu2 to the filed value, you will get the desired result.

[Jay]

Hi all,

Thanks for your suggestions. My colleague developed the solution.

First we add (sparcv9) to the beginning of 2nd record, store in a flat file, add a index column indicating CPUs belonging to Computer 1/2/3 etc.
Second we read from the flat file with (sparcv9) as the delimiter. Rebuild the index column and then join with the remaining columns ComputerID and Name.

All will be in Sequential. Hopefully there are not many cases like this.

I am on 8.1. The 8.5 looping functionality would be really handy in such cases.

Thanks Jay,
p.s. Coming back to Datastage after 5 yrs of Informatica is a fun experience for sure!!!