spilt the record into three records

panchusrao2656 · Post by **panchusrao2656** » Sat Sep 17, 2005 4:47 pm

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

ray.wurlod · Post by **ray.wurlod** » Sat Sep 17, 2005 6:28 pm

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.

panchusrao2656 · Post by **panchusrao2656** » Sat Sep 17, 2005 10:26 pm

thank you ray.

kumar_s · Post by **kumar_s** » Sun Sep 18, 2005 1:04 am

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

ray.wurlod · Post by **ray.wurlod** » Sun Sep 18, 2005 4:27 pm

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

kumar_s · Post by **kumar_s** » Mon Sep 19, 2005 12:03 am

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

cmmurari · Post by **cmmurari** » Mon Sep 19, 2005 4:40 am

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

kumar_s · Post by **kumar_s** » Mon Sep 19, 2005 10:57 am

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

ray.wurlod · Post by **ray.wurlod** » Mon Sep 19, 2005 4:15 pm

What version of Oracle? RTFM?

panchusrao2656 · Post by **panchusrao2656** » Mon Sep 19, 2005 11:35 pm

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

kumar_s · Post by **kumar_s** » Tue Sep 20, 2005 2:34 am

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

ray.wurlod · Post by **ray.wurlod** » Tue Sep 20, 2005 2:42 am

may i know what is RTFM???

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

panchusrao2656 · Post by **panchusrao2656** » Sun Sep 25, 2005 1:05 am

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

ray.wurlod · Post by **ray.wurlod** » Sun Sep 25, 2005 1:08 am

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