DSXchange

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

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.

thank you ray.

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

Don't forget that the input will require to be partitioned and sorted on the key column(s) for this technique to work properly.

ray.wurlod wrote:Don't forget that the input will require to be partitioned and sorted on the key column(s) for this technique to work properly.

I agree!!!

regards
kumar

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

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

Hi 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

What version of Oracle? RTFM?

thank you for your ideas.i solved that with a copy stage ,3 output links and one funnel stage.

Hi ray,
may i know what is RTFM???

Hi Rao,
Can you tell us how you manage to do using copy and funnel stage

regards
kumar

may i know what is RTFM???

Refer to the <<insert participle here>> manual.

i put three out put streams from the copy stage and changed the column names in the output and then i used a funnel at the end as each stream has the same columns.

Once agin thank you for your views.

Rao

Good that you have a working solution. You could have done it with one Pivot stage.