Page 1 of 2

Dont keep any, if duplicate

PostPosted: Wed Dec 14, 2011 7:17 pm
by LasseH
If record (part that I sort on) "already" exists, remove all these records
(or if "exists" and there is a record with 999 in col8-10)

IN:
AAAAAA 001
BBBBBBB 009
AAAAAA 999

OUT:
BBBBBB 009

//Lasse

Re: Dont keep any, if duplicate

PostPosted: Wed Dec 14, 2011 9:37 pm
by BillyBoyo
Do you mean

AAAAAA 001
AAAAAA 002
BBBBBB 789
CCCCCC 346
DDDDDD 999
EEEEEE 100


Gets you

BBBBBB 789
CCCCCC 346
EEEEEE 100


?

Re: Dont keep any, if duplicate

PostPosted: Wed Dec 14, 2011 11:14 pm
by Frank Yaeger
If you want to keep only non-duplicates, you can do that with the SELECT NODUPS function of DFSORT's ICETOOL.

SELECT FROM(IN) TO(OUT) ON(1,6,CH) NODUPS


For complete information on the SELECT operator, see:

http://publibz.boulder.ibm.com/cgi-bin/ ... 0608113434

(or if "exists" and there is a record with 999 in col8-10)


It isn't clear what you mean by this. You need to explain more clearly with a better example of input records ad expected output records that covers all cases.
Also, give the RECFM and LRECL of the input file.

Re: Dont keep any, if duplicate

PostPosted: Thu Dec 15, 2011 1:49 pm
by LasseH
Hi
Standard FB80

Sorting on 1,6 (key)
If 8,3 = 999 do not keep any of the records with the same key

IN:
aaaaaa 001
aaaaaa 999
bbbbbb 012
ccccccc 012
dddddd 013
dddddd 014
dddddd 999

OUT:
bbbbbb 012
ccccccc 012

//Lasse

Re: Dont keep any, if duplicate

PostPosted: Thu Dec 15, 2011 2:15 pm
by BillyBoyo
For clarity, can you include sample data with duplicates which do not include 999.

Re: Dont keep any, if duplicate

PostPosted: Thu Dec 15, 2011 2:31 pm
by LasseH
Just append one more row like

cccccc 013

Result: both cccccc rows shall end up in OUT

Re: Dont keep any, if duplicate

PostPosted: Thu Dec 15, 2011 2:52 pm
by BillyBoyo
Sorry, I think I took a wrong track from the subject you gave the topic.

So, if a particular key (1, 6) has one record with an indicator value of "999" then the whole group of records should be excluded, everything else should be included? Have I got it now?

Re: Dont keep any, if duplicate

PostPosted: Thu Dec 15, 2011 4:08 pm
by LasseH
BillyBoyo wrote:Sorry, I think I took a wrong track from the subject you gave the topic.

So, if a particular key (1, 6) has one record with an indicator value of "999" then the whole group of records should be excluded, everything else should be included? Have I got it now?


Yep, that's correct

Re: Dont keep any, if duplicate

PostPosted: Thu Dec 15, 2011 8:38 pm
by BillyBoyo
I'm sure a proper solution will arrive later.

You could exclude all but "999" to one file. Then use JOINKEYS with that file as the second against the original file. Take those that are unmatched and you have your data.

I'm thinking something like the following:

//DROP999  EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//INA      DD *                                                     
AAAAAA 001                                                         
AAAAAA 999                                                         
BBBBBB 012                                                         
CCCCC 012                                                         
DDDDDD 013                                                         
DDDDDD 014                                                         
DDDDDD 999                                                         
EEEEEE 010
EEEEEE 011
//INB      DD SYSOUT=*                                             
//SYMNAMES DD *                                                     
FILE-KEY,1,6,CH                                                     
RECORD-DONT-WANT-INDICATOR,8,3,CH                                   
RECORD-DONT-WANT-VALUE,C'999'                                       
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  JOINKEYS F1=INA,FIELDS=(FILE-KEY,A),SORTED,NOSEQCK               
  JOINKEYS F2=INB,FIELDS=(FILE-KEY,A),SORTED,NOSEQCK               
  JOIN UNPAIRED,F1,ONLY                                             
//*                                                                 
//JNF2CNTL DD *                                                     
 OPTION COPY                                                       
 INCLUDE COND=(RECORD-DONT-WANT-INDICATOR,EQ,RECORD-DONT-WANT-VALUE)
//*


Unfortunately I can't test it, and I'm only learning about DFSORT as well. I have hopes that it would work, but no idea or if optimal. My first JOINKEYS attempt :-)

Re: Dont keep any, if duplicate

PostPosted: Thu Dec 15, 2011 10:13 pm
by skolusu
BillyBoyo wrote:Unfortunately I can't test it, and I'm only learning about DFSORT as well. I have hopes that it would work, but no idea or if optimal. My first JOINKEYS attempt


Good Job for a first attempt. You got the syntax right, however your JCL had an error. Your INB DDname is pointing to sysout instead of the actual DSN name. JNF2CNTL control cards are starting in pos 1. You don't have to specify OPTION COPY on JNF2CNTL as the SORTED,NOSEQCK will treat it as COPY operation. Here is the updated version of the job
//DROP999  EXEC PGM=SORT     
//SYSOUT   DD SYSOUT=*       
//INA      DD DSN=Your input file,DISP=SHR
//INB      DD DSN=Same input file again,DISP=SHR
//SYMNAMES DD *                                       
FILE-KEY,1,6,CH                                       
RECORD-DONT-WANT-INDICATOR,8,3,CH                     
RECORD-DONT-WANT-VALUE,C'999'                         
//SORTOUT  DD SYSOUT=*                               
//SYSIN    DD *                                       
  OPTION COPY                                         
  JOINKEYS F1=INA,FIELDS=(FILE-KEY,A),SORTED,NOSEQCK 
  JOINKEYS F2=INB,FIELDS=(FILE-KEY,A),SORTED,NOSEQCK 
  JOIN UNPAIRED,F1,ONLY                               
//*                                                   
//JNF2CNTL DD *                                       
  INCLUDE COND=(RECORD-DONT-WANT-INDICATOR,EQ,       
                RECORD-DONT-WANT-VALUE)               
//*