Page 1 of 1
MERGE and Add a record
Posted:
Mon Feb 20, 2012 10:45 am
by Sushmita V
Hi,
I have two files:
FILE1:
NAME|DOB|ADDRESS
GEORGE|19841010|1ST STREET
TEENIEE|19821010|3RD STREET
FILE2:
NAME|DOB|ADDRESS
TOM|19801111|FRANKLIN BLVD
JERRY|19800101|ABC BLVD
JESSICA|19880203|LONDON
I need these two files to be merged and my output should be:
NAME|DOB|ADDRESS
GEORGE|19841010|1ST STREET
TEENIEE|19821010|3RD STREET
TOM|19801111|FRANKLIN BLVD
JERRY|19800101|ABC BLVD
JESSICA|19880203|LONDON
I can use simple SORT=COPY in my sysin card but it gives me below results:
NAME|DOB|ADDRESS
NAME|DOB|ADDRESS
GEORGE|19841010|1ST STREET
TEENIEE|19821010|3RD STREET
TOM|19801111|FRANKLIN BLVD
JERRY|19800101|ABC BLVD
JESSICA|19880203|LONDON
Please suggest!
Thanks & Regards,
Sushmita
Re: MERGE and Add a record
Posted:
Mon Feb 20, 2012 12:48 pm
by BillyBoyo
SORT=COPY?
Can you show what you actually used to create that output?
Re: MERGE and Add a record
Posted:
Mon Feb 20, 2012 10:05 pm
by skolusu
Sushmita V,
Use the following DFSORT JCL which will give you the desired results. I assumed that your both input files are of the same LRECL and RECFM . ie LRECL=80 and RECFM=FB
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
NAME|DOB|ADDRESS
GEORGE|19841010|1ST STREET
TEENIEE|19821010|3RD STREET
// DD *
NAME|DOB|ADDRESS
TOM|19801111|FRANKLIN BLVD
JERRY|19800101|ABC BLVD
JESSICA|19880203|LONDON
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
OUTREC IFTHEN=(WHEN=(1,5,CH,EQ,C'NAME|'),OVERLAY=(81:SEQNUM,1,ZD))
OUTFIL OMIT=(81,1,ZD,EQ,2),BUILD=(1,80)
//*
Re: MERGE and Add a record
Posted:
Tue Feb 21, 2012 7:08 pm
by Nik22Dec
Hi,
What Skolusu has suggested is as usual absolutely correct.
IMHO, You can also try using -
The only caveat is that the input files should not have the same/duplicate records otherwise, one of them will get ignored.
Re: MERGE and Add a record
Posted:
Tue Feb 21, 2012 7:23 pm
by BillyBoyo
Two extra caveats. Syntax. OPTION COPY.
Re: MERGE and Add a record
Posted:
Wed Feb 22, 2012 12:36 am
by Frank Yaeger
Nik22Dec,
As BB points out, you would need to do a SORT or a MERGE to use SUM FIELDS=NONE. That would be less efficient then Kolusu's COPY solution.
Re: MERGE and Add a record
Posted:
Wed Feb 22, 2012 2:27 pm
by Nik22Dec
BillyBoyo wrote:Two extra caveats. Syntax. OPTION COPY.
I am sorry Billy but, I am not sure if I understood your post clearly. Can you please elaborate.
Frank Yaeger wrote:Nik22Dec,
As BB points out, you would need to do a SORT or a MERGE to use SUM FIELDS=NONE. That would be less efficient then Kolusu's COPY solution.
Thanks for your reply Frank. Yes, a SORT or a Merge is defintely required. Thanks for pointing out at the efficiency aspect of my solution. I wasn't aware of that.
Re: MERGE and Add a record
Posted:
Wed Feb 22, 2012 3:12 pm
by BillyBoyo
Nik,
If we provide something, it is nice (read obligatory) if it gets through the syntax checking.
The SUM is not a possible solution in a COPY operation. If you SORT the files, you 1) might get other duplicates (your caveat) 2) destroy the order of the file. If you MERGE the files, the record in question is not contiguous with its twin, so would not come under the SUM anyway.
Re: MERGE and Add a record
Posted:
Mon Feb 27, 2012 11:55 am
by Nik22Dec
Hi Billy,
Yes! I agree!
I wanted to give TS a simpler idea which he can use with little effort. Unfortunately, I was under the wrong impression that the order of the records wouldn't mattter & that spoiled the entire thing.
Thanks for pointing that out.