INSERTING ROWS MULTIPLE TIMES

Poornagirija · Post by **Poornagirija** » Mon Jul 17, 2006 4:13 am

HI FOLKS, :D
We are working in DS7.5.1 on Unix.
We have requirement as below.
Example:source
Table A contains
a1 a2 a3
MQ2www deployed 3
MQ2xxx Inactive 2

where a1,a2,a3 are field names

TARGET:
We have to populate the target table as follows
If a3 = 3 then we have to insert the target table repeating that particular row 3 times
If a3 = 2 then we have to insert the target table repeating that particular row 2 times and so on

Target table is as follows:

TABLE B
a1 a2 a3
MQ2www deployed 3
MQ2www deployed 3
MQ2www deployed 3
MQ2xxx Inactive 2
MQ2xxx Inactive 2

IS there any functions available to implement the same in Datastage.(except writing and calling procedure)

Your inputs are valueable to us.

Many thanks in advance

ArndW · Post by **ArndW** » Mon Jul 17, 2006 4:25 am

How about using a transform stage with multiple outputs that have constraints on them so that your data is written multiple times depending on the value of field a3?

Poornagirija · Post by **Poornagirija** » Mon Jul 17, 2006 5:09 am

HI ArndW,
Thanks for your response.
Im perplexed

It will be great if you explain me in elaborate manner.

ArndW · Post by **ArndW** » Mon Jul 17, 2006 6:20 am

In you transform you have one input link and (let us assume just 3 maximum) 3 output links. In the constraint for the 2nd one, put "In.a3=>2" and output the row. In the constraint for the third one, put "In.a3=>3" and output the row. This way, if a3 = 3 then for each input row you will get 3 output rows. You will need to either use a link collector to merge these 3 streams into one for writing to a sequential file or make sure that all of your output links have some sort of a unique key if writing straight to the database.

sb_akarmarkar · Post by **sb_akarmarkar** » Mon Jul 17, 2006 6:28 am

Create a routine which uses concatenation a1:a2:a3 :char(10):char(13) in loop for no of a3 and output of routine to flat file .... This will help you ..

Thanks,
Anupam

Poornagirija · Post by **Poornagirija** » Mon Jul 17, 2006 7:13 am

HI ArndW,
The a3 is not always going to have 3 or 2.its just an example.
It may contain any no.

Thanks

Hi Anupam,
Thanks for your solution,let me try and get back to you.

chulett · Post by **chulett** » Mon Jul 17, 2006 7:27 am

For a little less... terse... example of the technique, check this FAQ posting on the same subject.

ArndW · Post by **ArndW** » Mon Jul 17, 2006 7:29 am

This thread is an exact repeat of one that happened last week. If you have a reasonable limit on the output links (say 30) then this method will work efficiently. The easy alternate is what Anupam has suggested, by using an algorithm to loop around creating multiple "lines" by inserting <cr><lf> into the string; this way you might just do one "write" in the job to the sequential file, but since you have added line breaks yourself you will get multiple "reads" off that one write. This is a very efficient method and you can save disk I/O by using a named pipe as your interim sequential file.

kumar_s · Post by **kumar_s** » Tue Jul 18, 2006 3:38 am

Another thought. Have a Table with the following values.

1
2
2
3
3
3
4
4
4
4....

to the maximum possible value of a3. (Which can be populated even at runtime by finding the max of a3 from TableA.)
Do a lookup to the Table A with a3 column. And get the Table A values.

Poornagirija · Post by **Poornagirija** » Mon Jul 24, 2006 10:14 pm

HI :D ,
Thanks for all your support.
I tried Anupams routine and its working fine.

Thanks Anupam.