INSERTING ROWS MULTIPLE TIMES
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 65
- Joined: Fri Nov 19, 2004 12:00 am
INSERTING ROWS MULTIPLE TIMES
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
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
With Regards,
Poornagirija.
"Don't limit your challenges - challenge your limits"
Poornagirija.
"Don't limit your challenges - challenge your limits"
-
- Participant
- Posts: 65
- Joined: Fri Nov 19, 2004 12:00 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.
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
-
- Participant
- Posts: 65
- Joined: Fri Nov 19, 2004 12:00 am
For a little less... terse... example of the technique, check this FAQ posting on the same subject.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 65
- Joined: Fri Nov 19, 2004 12:00 am