Extracting the Data from Base tables to a file


Build a FlatFileSchema with RecordDefinition Header(H)set maxRepeat=unlimited and under the same hierarchy create another RecordDefinition Details “F” set maxRepeat=unlimited and create one or more fields with fixedlentgth or variablelength to parse the flatfile.
Test run the Schema with some sample,if it works.

Extract the data from DB and map the H,F results to FFRecord and do convertToString then write the whole string to a file using WmSamples/string (WriteToFile)service.



The flatfile I mentioned above is what it’s going to look like when I extract the data from the database. My queries are ready to go. I can see the data when I run my package. Only thing is to spool/write the data I got from the queries to a file.

Do I still need to go through the same steps you have mentioned above?

Priyatham Porika

Yes it is one of the approach…

Hi Ram,

I am able to write the data to a file but The Header as well as detail records are coming in the same line.

This is what I was doing:
I have the following steps:
Select The Header Records From KSU_STARS_SOKI_JV_HDR
Select The Header Records From KSU_STARS_SOKI_JV_DTL
LOOP on /SelectJvSokiHdrOutput/results
Map the Header Data to IDATA(Document)
LOOP on /SelectJvSokiDetailsOutput/results
Map Details Data to IDATA (Document)
SEQUENCE (Try/Catch Block)

I can see the Header and Detail records in the same line.
What I am doing is in my first LOOP I map the Header records and
in the Detail LOOP I map only the detail records.

I tested the sample FLAT FILE SCHEMA whic is valid.All the record positions are perfect except
the header and detail lines are in the same line.

Can you please help me!!!

Thank You,

In the Flatfile schema of FlatFileEditor you set the Delimiter for Record to (newline),then when you do convertToString each record (Header,Details)will show in eachline per record.



I solved the above mentioned problem. Thanks for that.
Onemore thing Ram!!!

Here is my flow structure:

dbJvSokiOps:SelectJvSokiHdr (Select The Header Records From KSU_STARS_SOKI_JV_HDR)
dbJvSokiOps:SelectJvSokiDetails (Select The Detail Records From KSU_STARS_SOKI_JV_DETAIL)
SEQUENCE (Exit On Success)
SEQUENCE(Exit on Failure)
LOOP over /SelectJvSokiHdrOutput/results
Map Header Data to IDATA
LOOP over /SelectJvSokiDetailsOutput/results
Map Detail Data to IDATA
SEQUENCE (Exit On Done)

What I get is One header and One line again the same header and next different line. That means I have one header and two lines. I know I am not Looping it properly.

In my flat file I have FlatFileSchema Header as “H” and Line as “F” which is one level inside the header. Since I will have One haeder and multiple lines for that. So I have two queries one for header and another for details. I pass one of the primary key of header to details query as input and pull all the records for that primary key.

How can I get rid of the same header repeating?
Can you please help me out!!!

Thank you,
Priyatham Porika