IBM Mainframe Forum

Posted: **Fri Apr 30, 2010 1:50 am**

I hv 2 input files

File-a
student-Id
A00001
A00002
A00003
A00004

file-b
Student-id Score
A00001. 90
A00003. 39

Output
Student-id Score
A00001. 90
A00002. 00
A00003. 39
A00004. 00

Posted: **Fri Apr 30, 2010 1:54 am**

For all the students in file-a if score is not available in file-b then score should be set to 00.
Believe this is similar to overlay.
Can someone help?

Posted: **Fri Apr 30, 2010 2:48 am**

Hello Tina.

Yes, you can use OVERLAY in conjunction with JOIN to accomplish this task. Please see the SyncSort for z/OS job below:

Select all

//STEP1 EXEC PGM=SORT
//SORTJNF1 DD *
A00001
A00002
A00003
A00004
//SORTJNF2 DD *
A00001. 90
A00003. 39
//SORTOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,6,A)
JOINKEYS FILES=F2,FIELDS=(1,6,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,6,F2:7,8)
SORT FIELDS=COPY
OUTREC IFTHEN=(WHEN=(7,8,CH,EQ,C' '),OVERLAY=(7:C'. 00'))
/*

Posted: **Wed May 05, 2010 8:39 pm**

Alissa, this is a neat trick........I have a question on the statements used in SYSIN.

Why does it fail if I use JOIN UNPAIRED,F1 but goes through when JOIN UNPAIRED,F1,ONLY used.
What is the meaning of JOIN UNPAIRED,F1 & REFORMAT FIELDS combination?

Please explain.

Thanks.

Posted: **Wed May 05, 2010 10:00 pm**

What is the error you get when you specify JOIN UNPAIRED,F1?

Posted: **Fri May 07, 2010 5:40 pm**

Hi,

This is the error - I removed REFORMAT FIELDS and submitted this solution.

Select all

WER471A A REFORMAT STATEMENT IS REQUIRED TO USE THE JOIN FEATURE

Thanks.

Posted: **Fri May 07, 2010 7:22 pm**

Please post your entire JCL and sysouts.

Posted: **Fri May 07, 2010 10:30 pm**

As the message indicates, for JOIN applications, a REFORMAT statement is required (unless you are coding a "JOIN UNPAIRED,ONLY"). How else would the SORT know which fields from each of the input files you want included in the output?

Posted: **Fri May 07, 2010 10:51 pm**

ranga_subham wrote:Why does it fail if I use JOIN UNPAIRED,F1 but goes through when JOIN UNPAIRED,F1,ONLY used.
What is the meaning of JOIN UNPAIRED,F1 & REFORMAT FIELDS combination?

JOIN UNPAIRED,F1,ONLY indicates that you only want the unpaired records from SORTJNF1, not the paired/matched records.

JOIN UNPAIRED,F1 indicates that you want the paired records AND the unpaired records from SORTJNF1 (but not the unpaired records from SORTJNF2).

The REFORMAT statement specifies which fields from SORTJNF1 and SORTJNF2 are to be included in each record created by the JOIN processing.

When both JOIN UNPAIRED,F1 and the REFORMAT statement are specified, any fields not present will be filled with blanks* (X'40') by default.
*This can be changed by coding the FILL=f subparameter (where f specifies the fill byte) on the REFORMAT statement.

Posted: **Fri May 07, 2010 11:03 pm**

Thanks a lot.............thats a lot of info..........

IBM Mainframe Forum

Overlay in syncsort

Overlay in syncsort

Re: Overlay in syncsort

Re: Overlay in syncsort

Re: Overlay in syncsort

Re: Overlay in syncsort

Re: Overlay in syncsort

Re: Overlay in syncsort

Re: Overlay in syncsort

Re: Overlay in syncsort

Re: Overlay in syncsort