Inserting Blank Rows into a file

JezT · Post by **JezT** » Wed Oct 06, 2004 2:48 am

I am running a job that extracts data from a DB2 table and stores it in a sequential file. It is basically records of telephone calls received and is split into quarter hour slots during the day.

What I need to do is insert blank rows of data for every quarter hour slot within each day where no data is held. in other words, when no telephone calls were received.

An extract from the data I have at the moment, is shown below:

Code: Select all

CALL_QTR_SLOT, CALL_HALF_HR_SLOT
1,1
5,3
9,5
13,7
17,9

So basically I would need to add in blank lines for Quarter Hour Slots 2,3,4,6,7,8,10,11,12,14,15,16 etc.

Any suggestions ?

vigneshra · Post by **vigneshra** » Wed Oct 06, 2004 3:54 am

If I have understood your problem correctly, you can make use of Coalesce function in DB2 which substitutes a desired value whenever a null is returned from an expression. If you can explain more on what you need exactly, I can give you the pseudo SQL query.

Vignesh.

JezT · Post by **JezT** » Wed Oct 06, 2004 4:01 am

Vignesh

Basically, I need to end up with a Sequential file that has one row for every quarter hour slot in a day (so there should be 96 rows in the file). The majority of these rows will be obtained when the source data is extracted from the DB2 table. However, if there are no records for a specific quarter hour slot in a day, there is obviously no data to extract from the DB2 table and therefore no row will be produced.

Therefore, once all the data has been extracted from the table, passed through a Transformer and Aggregator to format and then sum certain parts of the data, it is passed into the target file.

At this point, there will not be the required 96 rows and here I will need to insert a row for each quarter hour slot that did not have any data in the table. The inserted row will need to have the relevant Quarter Hour Slot value and Half Hour Slot value and then 0's for all the other fields.

chulett · Post by **chulett** » Wed Oct 06, 2004 4:41 am

Me, I'd try to handle it on the database side. Not sure the particulars on DB2 (being an Oracle person) but you could create a reference table that contains all of your "quarter hour slots in a day". (or perhaps do something on the fly with the equivalent of a 'select from dual'?) Then simply do a union of your results and a MINUS of this table from your results to generate 100% of the records in your source query.

That's the first thing that popped into my head, anyway. But then it's early for me.

JezT · Post by **JezT** » Wed Oct 06, 2004 7:24 am

Craig

The only problem with that is the source table does not actually contain Quarter Hour Slots. It contains a time that the telephone call was received and then this is passed into a transformer which calculates which Quarter Hour Slot within the day that call falls into.

Jez

chulett · Post by **chulett** » Wed Oct 06, 2004 9:41 am

Work tables.

If you can't find a way to work this out all in DataStage, break it into a couple of pieces. First job transforms your source, does your Quarter Hour thing and then loads it up into a work table. Then you can do the union minus thing after that.

leomauer · Post by **leomauer** » Wed Oct 06, 2004 12:06 pm

How about that:
1. Create flat file of 96 rows with time slot values.
2. Load your file (the file with missing time slots) to hash file.
3. Lookup row in hash file.
Match found: move values from hash.
Match not found: move defaults (zeroes).

dhiraj · Post by **dhiraj** » Wed Oct 06, 2004 4:43 pm

If the output file produced by your job is in sorted order of the quarter number, then use an after job routine to do this

1) read the first rec from the file and write it to the output
2) read the next rec and compare the quarter number with that of the previous record. if not in order you expect, then insert a required number of blank records.
3) write the incoming record to output.

doing steps 2 and 3 in a loop until eof should give u what you require.

Dhiraj