IBM Mainframe Forum

by **bodatrinadh** » Wed Jan 25, 2012 3:29 pm

Hi Ushasandeep,

try this code

//S1A EXEC PGM=SORT
//$ORTPARM DD DSN=IMS.PARMLIB(IMSMS005),DISP=SHR
//SORTJNF1 DD *
0912345678900000100
1012345678900000200
0912123278900000300
//SORTJNF2 DD *
0912345678900000100
0212125678900000200
//SORTOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,10,A)
JOINKEYS FILES=F2,FIELDS=(1,10,A)
REFORMAT FIELDS=(F1:1,19,F2:1,19)
JOIN UNPAIRED,F1,F2
SORT FIELDS=(1,10,CH,A)
INCLUDE COND=(1,10,CH,NE,20,10,CH)
OUTREC IFTHEN=(WHEN=(1,1,CH,EQ,C' ',&,20,1,CH,NE,C' '),
BUILD=(1:20,19)),
IFTHEN=(WHEN=(1,1,CH,NE,C' ',&,20,1,CH,EQ,C' '),
BUILD=(1:1,19))

Thanks
-3nadh

by **BillyBoyo** » Wed Jan 25, 2012 3:38 pm

Bodrinath, what is the source of your two different files from the JOIN?

Why do you want to sort the file again? It is already in sorted order, according to the TS.

What is the point of the JOIN if you are then going to compare for include/omit? Or, to put it another way, what is the point of the include/omit if you do a proper JOIN.

by **bodatrinadh** » Wed Jan 25, 2012 4:27 pm

Billy,

TS is trying to achieve output through Joinkeys, so i give him a joinkeys solution.

Or it can be achived it by

Select all

//S1A EXEC PGM=SORT
//$ORTPARM DD DSN=IMS.PARMLIB(IMSMS005),DISP=SHR
//SORTIN DD *
0912345678900000100
1012345678900000200
0912123278900000300
// DD *
0912345678900000100
0212125678900000200
//SORTOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
INREC OVERLAY=(81:SEQNUM,7,ZD,RESTART=(1,10))
SORT FIELDS=(1,10,CH,A)
SUM FIELDS=(81,7,ZD)
OUTFIL OMIT=(81,7,CH,EQ,C'0000002')

Thanks
-3nadh

by **BillyBoyo** » Wed Jan 25, 2012 4:42 pm

You are missing the point. In fact several.

The data is already in the sequence required.

JOINKEYS does not need to have a SORT with it (indeed, by default it will itself sort the records, so you will be sorting twice on the same key a file which is already in key order).

You are using two different input files to the JOIN. Where did you get the second file from? Your data, unlike TS's, is not sorted.

If you can code a join, using the same input file for both the indput DDs, using the control cards and other things I suggested, I'm sure it would be appreciated.

If you just want to provide something that has the word "JOINKEYS" in it, I don't know how that helps.

TS does not want a JOINKEYS solution. I suggested it. They want a solution to the one-liner in the Tool which works perfectly, but which they are not allowed to use in production.

The file is already in sequence, so wants to know how to exclude all of a group of records which has duplicates. If duplicates for a key, no records for that key on the output.

by **enrico-sorichetti** » Wed Jan 25, 2012 4:43 pm

instead of

Select all

OUTFIL OMIT=(81,7,CH,EQ,C'0000002')

it would be better to use

Select all

OUTFIL OMIT=(81,7,ZD,GT,1)

to consider more than 2 occurrences of a duplicate

also why to bother with a sequence number when

Select all

INREC OVERLAY=(81:C'00000001')

would be more than enough

by **BillyBoyo** » Wed Jan 25, 2012 5:01 pm

We will now have a thoroughly confused TS.

TS require no data at all for a particular key if that particular key has duplicates.

The file is already in sequence.

It can be done in two steps by attaching a sequence number, sorting descending on that after the main key, then sorting again on the main key only with SUM FIELDS=NONE and an OMIT after that for the sequence number being greater than one, then BUILD to drop the sequence number.

I've been suggesting the JOINKEYS to avoid all the extra sorts.

This is an excellent example of the nonesense of not allowing "Tool" solutions in production. But, those are the rules TS is operating under. If they haven't by now slipped quietly away to bash their head against a brick wall.

by **enrico-sorichetti** » Wed Jan 25, 2012 5:18 pm

the file is not in sequence ( see the sample data from the TS )
and anyway You will need to use a sort clause in order to have a key to SUM Over
so the last solution posted ( after my suggestions ) is not bad at all

Select all

000023 OPTION EQUALS
000024 INREC OVERLAY=(81:C'00000001')
000025 SORT FIELDS=(1,10,CH,A)
000026 SUM FIELDS=(81,8,ZD)
000027 OUTFIL OMIT=(81,8,ZD,GT,1),BUILD=(1,80)

by **BillyBoyo** » Wed Jan 25, 2012 5:52 pm

BillyBoyo wrote:Is your data already sorted?

What level of DFSORT do you have (post the ICE201I message, please)?

USHASANDEEP wrote:Hi BillyBoyo,

yeah. file is sorted. here the interface is SYNCSORT itseems. so, by default SYNCSORT will be invoked.
please find the below screen shot.

Seems I should have asked a different question :-)

Apologies for not looking at the data to verify the answer.

If not already sorted, less of a problem in sorting it (once) :-)

EDIT: I have to say I wouldn't go for EQUALS if all the equal keys are being dropped :-)

by **enrico-sorichetti** » Wed Jan 25, 2012 6:08 pm

the OPTION EQUALS was a leftover from a cannibalized sort

by **NicC** » Thu Jan 26, 2012 2:21 am

Hasn't this gone on long enough in the JCL section? Can it not be moved to the correct section - i.e. Syncsort?

IBM Mainframe Forum

Using SORT, is it possible to remove all duplicate occurs?

Re: Using SORT, is it possible to remove all duplicate occur

Re: Using SORT, is it possible to remove all duplicate occur

Re: Using SORT, is it possible to remove all duplicate occur

Re: Using SORT, is it possible to remove all duplicate occur

Re: Using SORT, is it possible to remove all duplicate occur

Re: Using SORT, is it possible to remove all duplicate occur

Re: Using SORT, is it possible to remove all duplicate occur

Re: Using SORT, is it possible to remove all duplicate occur

Re: Using SORT, is it possible to remove all duplicate occur

Re: Using SORT, is it possible to remove all duplicate occur