Page 1 of 1

MERGE and Add a record

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: Tue Feb 21, 2012 7:08 pm
by Nik22Dec
Hi,

What Skolusu has suggested is as usual absolutely correct. :D

IMHO, You can also try using -

Sum Fields = None


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

PostPosted: Tue Feb 21, 2012 7:23 pm
by BillyBoyo
Two extra caveats. Syntax. OPTION COPY.

Re: MERGE and Add a record

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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. :oops: Thanks for pointing that out.