Page 2 of 2

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

PostPosted: Wed Jan 25, 2012 3:29 pm
by bodatrinadh
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

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

PostPosted: Wed Jan 25, 2012 3:38 pm
by BillyBoyo
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.

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

PostPosted: Wed Jan 25, 2012 4:27 pm
by bodatrinadh
Billy,

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

Or it can be achived it by

//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

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

PostPosted: Wed Jan 25, 2012 4:42 pm
by BillyBoyo
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.

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

PostPosted: Wed Jan 25, 2012 4:43 pm
by enrico-sorichetti
instead of
OUTFIL OMIT=(81,7,CH,EQ,C'0000002')

it would be better to use
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
INREC OVERLAY=(81:C'00000001')

would be more than enough

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

PostPosted: Wed Jan 25, 2012 5:01 pm
by BillyBoyo
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.

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

PostPosted: Wed Jan 25, 2012 5:18 pm
by enrico-sorichetti
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


 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)         

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

PostPosted: Wed Jan 25, 2012 5:52 pm
by BillyBoyo
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 :-)

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

PostPosted: Wed Jan 25, 2012 6:08 pm
by enrico-sorichetti
the OPTION EQUALS was a leftover from a cannibalized sort ;)

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

PostPosted: Thu Jan 26, 2012 2:21 am
by NicC
Hasn't this gone on long enough in the JCL section? Can it not be moved to the correct section - i.e. Syncsort?