MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Icetool + get duplicates based on two fields.

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
priya173
Beginner


Joined: 04 May 2006
Posts: 10
Topics: 6
Location: India

PostPosted: Sat Aug 23, 2008 4:34 am    Post subject: Icetool + get duplicates based on two fields. Reply with quote

Hi,

I need some help on ICETOOL. I have a dataset with Customer#, Old Emp Id and New Emp ID as shown below

Cust# Old Emp ID New Emp ID
11111 aaaaaa bbbbb
11111 bbbbbb ccccc
11111 bbbbbb ddddd
11111 cccccc eeeee
11111 dddddd eeeee
22222 aaaaaa ddddd
22222 bbbbbb ddddd
33333 cccccc aaaaa

I want the duplicates in (Cust# and Old Emp ID) and (Cust# and New Emp Id) in a new file.
With Select and Nodups option I was able to get the duplicates in Old Emp ID and New Emp ID alone but I need to check the concatenated key (Cust# and Old Emp ID) and (Cust# and New Emp Id) and is there a way to give two field positions in the ON parameter?
I also want the header to be printed and used the Display and Header option, similarly is there a way to print the trailer with count of duplicate records alone? Kindly help.

Output:

Duplicates in Old Emp ID

Cust# Old Emp ID New Emp ID
11111 bbbbbb ccccc
11111 bbbbbb ddddd

Duplicates : 2

Duplicates in New Emp ID

Cust# Old Emp ID New Emp ID
11111 cccccc eeeee
11111 dddddd eeeee
22222 aaaaaa ddddd
22222 bbbbbb ddddd
Duplicates : 4
_________________
Regards,
Subha
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose

PostPosted: Sat Aug 23, 2008 12:17 pm    Post subject: Reply with quote

Quote:
is there a way to give two field positions in the ON parameter?


Yes, with two ON fields. See the following for the syntax of SELECT:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/6.11?DT=20060615185603

Quote:
With Select and Nodups option I was able to get the dup


That would be SELECT with ALLDUPS, not NODUPS.

Here's a DFSORT/ICETOOL job that will do what you asked for. I assumed you want the carriage control characters for the report, but if not add REMOVECC to each OUTFIL statement. Also, it wasn't clear where your fields started in the input records, so I guessed - adjust as needed.

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN DD *
11111   aaaaaa          bbbbb
11111   bbbbbb          ccccc
11111   bbbbbb          ddddd
11111   cccccc          eeeee
11111   dddddd          eeeee
22222   aaaaaa          ddddd
22222   bbbbbb          ddddd
33333   cccccc          aaaaa
/*
//OUT1 DD SYSOUT=*
//OUT2 DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN) TO(OUT1) ON(1,5,CH) ON(9,6,CH) ALLDUPS USING(CTL1)
SELECT FROM(IN) TO(OUT2) ON(1,5,CH) ON(25,5,CH) ALLDUPS USING(CTL2)
//CTL1CNTL DD *
  OUTFIL FNAMES=OUT1,
    HEADER1=('Duplicates in Old Emp ID',/,X,/,
     'Cust#   Old Emp ID      New Emp ID'),
    TRAILER1=(/,'Duplicates : ',COUNT=(EDIT=(IIT)))
/*
//CTL2CNTL DD *
  OUTFIL FNAMES=OUT2,
    HEADER1=('Duplicates in New Emp ID',/,X,/,
     'Cust#   Old Emp ID      New Emp ID'),
    TRAILER1=(/,X,/,'Duplicates : ',COUNT=(EDIT=(IIT)))
/*

_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group