Creating new records in DataStage

[prasad111]

Hi,

Please help me in doing this.

--INPUT RECORDS
-------------------------------------
ColA|ColB|ColC|ColD
A1 | A2 | ALL |A4
B1 | B2 | B3 |B4
C1 | C2 | C3 |C4
-----------------------------------------
Whenever I found the word ALL in ColC, I have to create a new 3 records, the value of ColA, ColB and ColD will be same. Instead of ALL I should have the value X in one record, Y in one record and Z in one record
-----------------------------------------------------------------
I want the output records like
----------------------------------------
ColA|ColB|ColC|ColD
A1 | A2 | X | A4
A1 | A2 | Y | A4
A1 | A2 | Z | A4
B1 | B2 | B3 | B4
C1 | C2 | C3 | C4
-----------------------------------------

Please let me know, how can I do in Datastage,
Regards,
Prasad

[vmcburney]

Tricky, DataStage isn't great at creating new rows. You could have four outputs from a transformer, put a constraint on each: three "input.col3 = 'ALL'" links hard coded to X, Y and Z and one "input.col3 <> 'ALL'" that passes through a value. After the transformer you would need to merge the four streams back into one.

[ShaneMuir]

Can you use a stage variable to do a replace on the incoming row?

Basically check to see if the incoming column is "ALL". If so replace the row with a concatenation of the 3 required rows separated by a line seperator.

ie variable output would be something like ColA:'|':ColB:'|X|':ColC:Char(10):ColA:'|':ColB:'|Y|':ColC:Char(10):ColA:'|':ColB:'|Z|':ColC:Char(10)

If you write the output to a sequential file this output would be interperted as 3 separate rows.

I am not sure if the syntax above is totally correct, but I am reasonable certain that it should work.

[I_Server_Whale]

Yes ! You can use stage variables as mentioned by Shane. I had done this way before.

But this solution is good if the target is a sequential file.

Otherwise you would have to use a sequential file as an intermediate stage.

Thanks,
Whale.

[prasad111]

Can you use a stage variable to do a replace on the incoming row?

Basically check to see if the incoming column is "ALL". If so replace the row with a concatenation of the 3 required rows separated by a line seperator.

ie variable output would be something like ColA:'|':ColB:'|X|':ColC:Char(10):ColA:'|':ColB:'|Y|':ColC:Char(10):ColA:'|':ColB:'|Z|':ColC:Char(10)

If you write the output to a sequential file this output would be interperted as 3 separate rows.

I am not sure if the syntax above is totally correct, but I am reasonable certain that it should work.

Yes this works, I used seqFile as the intermediate stage and insert in the OraOci stage