View previous topic :: View next topic
Author
Message
oerdgie New User Joined: 22 Nov 2007Posts: 64 Location: Germany
Hi folks,
can anybody help me to solve the following problem ?
File 1 has a non-duplicate key on pos 1-4.
File 2 has a matching or non-machting duplicate key on pos 1-4.
My result file should contain all records from file 1 and a flag 'Y' at the end
of the record when found a matching record in file 2
Example:
File 1:
1111AA
2222BB
3333CC
4444DD
File 2:
1111
3333
3333
Result:
1111AAY
2222BB
3333CCY
4444DD
Thanks for help in advance !
Back to top
nelson.pandian Active User Joined: 09 Apr 2008Posts: 133 Location: Phoenix, AZ
Hi oerdgie,
The Below DFSORT/ICETOOL JOB will give you the desired output. JOB attributes can be changed accordingly.
Code:
//STEP10 EXEC PGM=SORT
//*
//SYSOUT DD SYSOUT=*
//*
//SORTJNF1 DD *
1111AA
2222BB
3333CC
4444DD
/*
//*
//SORTJNF2 DD *
1111
3333
3333
/*
//*
//OUT DD SYSOUT=*
//*
//SYSIN DD * *** CONSTANT CONTROL CARDS ***
JOINKEYS FILES=F1,FIELDS=(1,4,A)
JOINKEYS FILES=F2,FIELDS=(1,4,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,6,F2:5,6,?)
OPTION COPY
OUTFIL FNAMES=OUT,BUILD=(1,7),
INCLUDE=(13,1,CH,EQ,C'1',OR,8,5,ZD,EQ,1)
/*
//JNF2CNTL DD *
INREC OVERLAY=(5:C'Y',6:SEQNUM,5,ZD,RESTART=(1,1))
/*
OUTPUT:
Code:
1111AAY
2222BB
3333CCY
4444DD
Back to top
sqlcode1 Active Member Joined: 08 Apr 2010Posts: 577 Location: USA
oerdgie,
See if below works...
Code:
//STEP0001 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD *
1111AA
2222BB
3333CC
4444DD
/*
//SORTJNF2 DD *
1111
3333
3333
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,4,A)
JOINKEYS FILES=F2,FIELDS=(1,4,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,6,?)
INREC IFTHEN=(WHEN=(7,1,CH,EQ,C'B'),OVERLAY=(8:C'Y'))
SORT FIELDS=COPY
OUTFIL BUILD=(1,6,8,1)
/*
//JNF2CNTL DD *
SUM FIELDS=NONE
/*
OUTPUT
Code:
1111AAY
2222BB
3333CCY
4444DD
Thanks,
Back to top
sqlcode1 Active Member Joined: 08 Apr 2010Posts: 577 Location: USA
nelson.pandian ,
Run your job with below input and observe results...
Code:
//SORTJNF1 DD *
1111AA
1234AA
2222BB
3333CC
4444DD
/*
//SORTJNF2 DD *
1111
1234
1234
3333
3333
/*
Thanks,
Back to top
oerdgie New User Joined: 22 Nov 2007Posts: 64 Location: Germany
Hi,
first, thanks all for help !
Sorry nelson.pandian, but sqlcode1 is right, your SORT doesn't work correct for me.
I now trying the example from sqlcode1...
Back to top
oerdgie New User Joined: 22 Nov 2007Posts: 64 Location: Germany
... and it works for me !
Many thanks
oerdgie
Back to top
nelson.pandian Active User Joined: 09 Apr 2008Posts: 133 Location: Phoenix, AZ
Hi,
@sqlcode1: You are correct. I missed to add the whole key in RESTART. Its my mistake.
The below code will work as the Key is included in the RESTART.
Code:
//STEP10 EXEC PGM=SORT
//*
//SYSOUT DD SYSOUT=*
//*
//SORTJNF1 DD *
1111AA
2222BB
3333CC
4444DD
/*
//*
//SORTJNF2 DD *
1111
3333
3333
/*
//*
//OUT DD SYSOUT=*
//*
//SYSIN DD * *** CONSTANT CONTROL CARDS ***
JOINKEYS FILES=F1,FIELDS=(1,4,A)
JOINKEYS FILES=F2,FIELDS=(1,4,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,6,F2:5,6,?)
OPTION COPY
OUTFIL FNAMES=OUT,BUILD=(1,7),
INCLUDE=(13,1,CH,EQ,C'1',OR,8,5,ZD,EQ,1)
/*
//JNF2CNTL DD *
INREC OVERLAY=(5:C'Y',6:SEQNUM,5,ZD,RESTART=(1,4))
/*
Back to top
Skolusu Senior Member Joined: 07 Dec 2007Posts: 2205 Location: San Jose
nelson.pandian,
You are assuming that the input is sorted the key. What happens if it is not ? Check and see if your solution works for the following data for SORTJNF2
Code:
//SORTJNF2 DD *
1111
3333
2222
3333
/*
Sqlcode1 has the right approach.
Back to top
Please enable JavaScript!