Merging two files with more than one criteria

raam_kumar · Posted: Fri Jan 18, 2008 10:38 am

Hi
i have the following requirement

there are 2 flat files (both are of same lrecl)
each file contains records like this
1) control record (record 1 of file)
2) datarecords (from record 2 to last but one record)
3) summary record (last record of a file)

For Eg:-
File 1
weekly report
data1
data2
data3
306(summary record which says total count of records and sum of field5)

File 2
weekly report
data4
data5
data6
315 (summary record which says total count of records and sum of field5)

Expected output
Weekly report
data1
data2
data3
data4
data5
data6
621

i.e output file should be in such a way that it should contain one control record, merge all the data record and one summary record which says total records and sum of fieldposition5

can any one help me with a sort card for this?

Frank Yaeger · Posted: Fri Jan 18, 2008 10:58 pm

This is actually quite tricky. But here's a DFSORT/ICETOOL job that will do it:

raam_kumar · Posted: Wed Jan 23, 2008 4:08 pm

Thank you Frank. but the requirement has changed now. and the new requuirement is

File 1 (LRECL=70):-
AAA 07365 12345
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
...
....
CCC 07365 12345 00000012129 000000011111111 .....

File 2 (LRECL 70):-
AAA 07365 12345
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
...
....
CCC 07365 12345 00000021219 000000011111111 .....

Expected output:-
AAA 07365 12345
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
...
....
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
BBB 07365 0123XXX21536......
...
....
CCC 07365 12345 00000033348 000000022222222 .....

00000033348 --> This is sum of trailer records (from position 16 to 26)

000000022222222 --> This is sum of trailer records (from position 28 to 42)

raam_kumar · Posted: Wed Jan 23, 2008 4:16 pm

hi

i have also tried by myself to create a sort card for this requirement. But i was unable to do the summing functionality. the sort card which i used is as follows

//STEP0001 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//INPUT DD DISP=SHR,DSN=<dsn1>
// DD DISP=SHR,DSN=<dsn2>
//TEMP1 DD SYSOUT=*
//TEMP DD DSN=&&TEMPDSN,
// DISP=(NEW,PASS),
// SPACE=(TRK,(5,5))
//OUTPUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(INPUT) TO(OUTPUT) USING(CTL1)
SORT FROM(INPUT) TO(OUTPUT) USING(CTL2)
COPY FROM(INPUT) TO(TEMP) USING(CTL3)
SORT FROM(TEMP) TO(TEMP1) USING(CTL5)
/*
//CTL1CNTL DD *
SORT FIELDS=COPY,
STOPAFT=1
//CTL2CNTL DD *
SORT FIELDS=COPY
OMIT COND=(1,3,CH,EQ,C'AAA',OR,1,3,CH,EQ,C'CCC')
/*
//CTL3CNTL DD *
SORT FIELDS=COPY
INCLUDE COND=(1,3,CH,EQ,C'CCC')
/*
//CTL5CNTL DD *
SORT FIELDS=COPY
SUM FIELDS=(16,11,ZD,28,15,ZD)

The problem is i am not able to produce the trailer record (i.e CCC) with the sum. i was able to get the trailer seperately, but could not find the sum values. could you please help me

Frank Yaeger · Posted: Wed Jan 23, 2008 11:21 pm

For this new requirement, we can take advantage of the ids in the record (AAA for header, BBB for detail and CCC for trailer) to do what you want with DFSORT in one pass as follows: