Parent Child Records in Flat File

[btmorris3]

I need to create a flat file from 2 Oracle tables. The Oracle tables have a parent/child relationship and the flat file needs to reflect this relationship in the following format:

Parent Record 1
child record 1a
child record 1b
child record 1c
Parent Record 2
child record 2a
child record 2b

etc...etc...

First of all, is this possible in DataStage? If so, how do I do it?

Thanks a million

[pnchowdary]

Hi Morris,

I believe that the parent/child relationship can be modelled in datastage using complex flat file stage. Dsx Gurus, please correct me, if I am wrong.

[lshort]

There may be a better way but this works:

Step1 write parent and detail rows out to seperate delimited files (i used comma)

Step2 using a different delimiter (|) write out parent rows with a fakekey equal to RealKey:".":0 as F1 and the entire row as F2

2.0|2,"Clownarama",11
4.0|4,"Aerial Extravaganza",5
6.0|6,"Grande Finale",6

Step3 Do the same with child rows appending to previous file. This time fakekey is RealKey:".":@OUTROWNUM

2.0|2,"Clownarama",1
14.0|4,"Aerial Extravaganza",5
6.0|6,"Grande Finale",6
2.1|2,87
2.2|2,43
2.3|2,65
2.4|2,30
4.5|4,7
6.6|6,82
6.7|6,54

Step4 Sort rows by FakeKey; output only the detail.

2,"Clownarama",11
2,87
2,43
2,65
2,30
4,"Aerial Extravaganza",5
4,7
6,"Grande Finale",6
6,82
6,54