How to write multiple records from 1 input record

harsh · Post by **harsh** » Thu Dec 12, 2002 7:33 am

We are using DataStage XE 5.2 on HP Unix server. In one of my job I have to read a Sequential File and after performing some business rules write to a Sequential file. The input file has some amount fileds which are occuring multiple times (say OCCURS 5 times)and if those fileds are NOT 0 then I have to write multiple records in the output file. The output file can have 0 to 5 records for each input record (depends upon amount field being zero or not). I am not sure what is the best way in DataStage to handle this kind of situation (Read 1 record and write multiple records). As a workaround I created 5 output sequential files and wrote the records so that I can read one record and write one record to a file. Then I merged these 5 files into one and then did my processing. Is there a better way ?

chulett · Post by **chulett** » Thu Dec 12, 2002 9:29 am

Check out the Pivot stage. There should be a pdf document that describes it in detail on your server cdrom.

-craig

ray.wurlod · Post by **ray.wurlod** » Thu Dec 12, 2002 3:37 pm

Pivot stage sounds exactly the tool for the kind of thing you're trying to do.
FWIW, there is another way where you load the data into a UniVerse table with columns defined as multi-valued, then select from the same table normalized no the multi-valued columns.