Dont keep any, if duplicate



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Dont keep any, if duplicate

Postby LasseH » Wed Dec 14, 2011 7:17 pm

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
LasseH
 
Posts: 50
Joined: Mon Nov 08, 2010 2:51 pm
Has thanked: 3 times
Been thanked: 1 time

Re: Dont keep any, if duplicate

 

Re: Dont keep any, if duplicate

Postby BillyBoyo » Wed Dec 14, 2011 9:37 pm

Do you mean

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


Gets you

BBBBBB 789
CCCCCC 346
EEEEEE 100


?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Dont keep any, if duplicate

Postby Frank Yaeger » Wed Dec 14, 2011 11:14 pm

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.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Dont keep any, if duplicate

Postby LasseH » Thu Dec 15, 2011 1:49 pm

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
LasseH
 
Posts: 50
Joined: Mon Nov 08, 2010 2:51 pm
Has thanked: 3 times
Been thanked: 1 time

Re: Dont keep any, if duplicate

Postby BillyBoyo » Thu Dec 15, 2011 2:15 pm

For clarity, can you include sample data with duplicates which do not include 999.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Dont keep any, if duplicate

Postby LasseH » Thu Dec 15, 2011 2:31 pm

Just append one more row like

cccccc 013

Result: both cccccc rows shall end up in OUT
LasseH
 
Posts: 50
Joined: Mon Nov 08, 2010 2:51 pm
Has thanked: 3 times
Been thanked: 1 time

Re: Dont keep any, if duplicate

Postby BillyBoyo » Thu Dec 15, 2011 2:52 pm

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?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Dont keep any, if duplicate

Postby LasseH » Thu Dec 15, 2011 4:08 pm

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
LasseH
 
Posts: 50
Joined: Mon Nov 08, 2010 2:51 pm
Has thanked: 3 times
Been thanked: 1 time

Re: Dont keep any, if duplicate

Postby BillyBoyo » Thu Dec 15, 2011 8:38 pm

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 :-)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Dont keep any, if duplicate

Postby skolusu » Thu Dec 15, 2011 10:13 pm

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)               
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post