Generate multiple records from a single source record

[jojipjohn]

I have two columns coming from the source (Oracle10g Database), one is a varchar and another interger. I have to generrate multiple rows for each rows coming from the source based on the integer column. Can someone tell me how to do this in Datastage.

[bryan]

You said based on the source column. More info would be helpful in understanding what you looking for.

If your input columns are more than two, using a pivot stage you can build multiple records from a single record . Please check pivot doc for more information.

Here is an eg: A record with four columns -- x,a,b,c.

Output of pivot stage would be
x,a
x,b
x,c

[jojipjohn]

e.g.

A recored with two columns: 'ABC1', 5

I want the output to be

'ABC1'
'ABC2'
'ABC3'
'ABC4'
'ABC5'

The number of output rows is based on the value of the integer coloumn.

[DSguru2B]

You can easily do this by a custom basic routine. Feed the two coloumns in the routine, have a For loop from 1 to n where n is your second column name and keep incrementing the value and keep concatenating it with the first coloumn's value and with char(10).
10 is the ascii code for EOL. char(10) will put an end of line character at the end.
Something like

Code: Select all

For i = 1 to colB
    colA=colA:i:char(10)
Next i
Ans = colA

This will produce an empty line after each row. To handle that just pass it through the transformer and control empty column in the constraint.
Regards,