Page 1 of 1
Parent Child Records in Flat File
Posted: Tue Jul 05, 2005 9:37 am
by 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
Posted: Tue Jul 05, 2005 10:23 am
by 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.
Posted: Tue Jul 05, 2005 3:01 pm
by 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