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
Creating new records in DataStage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
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.
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.
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
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.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Yes this works, I used seqFile as the intermediate stage and insert in the OraOci stageShaneMuir wrote: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.