spilt the record into three records
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 64
- Joined: Sat Sep 17, 2005 10:42 am
spilt the record into three records
i have a record with 4 columns key,A,B,C.I want to split the record into three records with the columns as key,type.
SOURCE:
key A B C
01 2 3 4
Target:
key TYPE VAL
01 A 2
01 B 3
01 C 4
IS THERE ANY SHORT CUT WITH OUT USING ONE COPY STAGE, THREE MODIFY STAGES AND ONE FUNNEL.
THANKS IN ADVANCE
RAO
SOURCE:
key A B C
01 2 3 4
Target:
key TYPE VAL
01 A 2
01 B 3
01 C 4
IS THERE ANY SHORT CUT WITH OUT USING ONE COPY STAGE, THREE MODIFY STAGES AND ONE FUNNEL.
THANKS IN ADVANCE
RAO
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Welcome aboard! :D
What you are describing here is called a "horizontal pivot" and can be performed by a Pivot stage (its stage type for parallel jobs is PivotPX).
Unfortunately you will not find this stage type in the DataStage manuals or in on-line help. It seems to have been constructed as a BuildOp (the generated OSH refers to dscapiop rather than a standard Orchestrate operator).
Someone experienced with DataStage could figure out how to drive it; your post count suggests that you are not in this category. Therefore I recommend that you hassle your support provider for documentation on the parallel Pivot stage. (Walk in and sing a couple of bars of Alice's Restaurant may help too.)
Another strategy is to use a server Pivot stage in a server shared container within the parallel job - there IS documentation for the server Pivot stage.
What you are describing here is called a "horizontal pivot" and can be performed by a Pivot stage (its stage type for parallel jobs is PivotPX).
Unfortunately you will not find this stage type in the DataStage manuals or in on-line help. It seems to have been constructed as a BuildOp (the generated OSH refers to dscapiop rather than a standard Orchestrate operator).
Someone experienced with DataStage could figure out how to drive it; your post count suggests that you are not in this category. Therefore I recommend that you hassle your support provider for documentation on the parallel Pivot stage. (Walk in and sing a couple of bars of Alice's Restaurant may help too.)
Another strategy is to use a server Pivot stage in a server shared container within the parallel job - there IS documentation for the server Pivot stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi,
You have another workaround to avoid pivot stage.
Use a transformer to make a single stream to three stream.
Each has its own constrians as
Type = 'A' [pass the data which maps VAL to key A and rest empty]
Type = 'B' [pass the data which maps VAL to key B and rest empty]
Type = 'c' [pass the data which maps VAL to key C and rest empty]
Use a aggregator to group based up on key and find the max for rest of the column.
regards
kumar
You have another workaround to avoid pivot stage.
Use a transformer to make a single stream to three stream.
Each has its own constrians as
Type = 'A' [pass the data which maps VAL to key A and rest empty]
Type = 'B' [pass the data which maps VAL to key B and rest empty]
Type = 'c' [pass the data which maps VAL to key C and rest empty]
Use a aggregator to group based up on key and find the max for rest of the column.
regards
kumar
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi krish,cmmurari wrote:if your DB source is Oracle you can achive this at DB Select statement level Or if you are strick to do this at Datastage Level you can use pivot stage. i do prefer to implement this by using Pivot stage
cheers,
Krish
I too prefer a single stage to 3 or more stage unless there come any constraints.
BTW do we have any PIVOT funciton in oracle
regards
kumar
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Charter Member
- Posts: 64
- Joined: Sat Sep 17, 2005 10:42 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Charter Member
- Posts: 64
- Joined: Sat Sep 17, 2005 10:42 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: