how to get the first two record when it was duplicated



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

how to get the first two record when it was duplicated

Postby userTesting » Fri Apr 09, 2010 12:23 pm

i had an input file sort by acc number Asc and date Desc
how to get below output?
input:
12342569 tom 20100101
25678990 david 20090210
25678990 david 20090210
25678990 david 20070101
53647859 loius 20081023
53647859 loius 20080123
74677899 ricky 20100512
74677899 ricky 20090512
74677899 ricky 20090512



output:will get the latest date record ,
if there had duplicate will get the 2 record out
12342569 tom 20100101
25678990 david 20090210
25678990 david 20090210
53647859 loius 20080123
74677899 ricky 20100512


thanks
userTesting
 
Posts: 8
Joined: Fri Apr 09, 2010 12:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: how to get the first two record when it was duplicated

 

Re: how to get the first two record when it was duplicated

Postby Frank Yaeger » Fri Apr 09, 2010 9:03 pm

For the input records:

53647859 loius 20081023
53647859 loius 20080123

You show the expected output as:

53647859 loius 20080123

But the latest date record has this output record:

53647859 loius 20081023

Did you just pick the wrong record or is there some rule you haven't mentioned that gives the other record as output?

Also, what is the RECFM and LRECL of the input file? What is the starting position, length and format of each field?

if there had duplicate will get the 2 record out


Is two the maximum number of duplicate records that can have the same date, or can there be more than two? If there are more than two with the same latest date, do you want them all in the output?
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: how to get the first two record when it was duplicated

Postby userTesting » Sat Apr 10, 2010 9:01 pm

hi

yes i pick the wrong record.
it should be

12342569 tom 20100101
25678990 david 20090210
25678990 david 20090210
53647859 loius 20081023
74677899 ricky 20100512

record format=fb length=450
acc (1,8)
date(16,8)


if more then two is the latest date i also need them to be show out.
all records in latest date group by account will be show out.
thanks
userTesting
 
Posts: 8
Joined: Fri Apr 09, 2010 12:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: how to get the first two record when it was duplicated

Postby skolusu » Mon Apr 12, 2010 9:40 pm

usertesting,

The following DFSORT JCL will give you the desired results

//STEP0100 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTIN   DD DSN=your input fb 450 byte file,DISP=SHR
//SORTOUT  DD SYSOUT=* 
//SYSIN    DD *                                                     
  SORT FIELDS=(1,8,CH,A,16,8,CH,D),EQUALS                           
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(451:SEQNUM,8,ZD,RESTART=(1,8))),
  IFTHEN=(WHEN=GROUP,BEGIN=(451,8,ZD,EQ,1),PUSH=(459:16,8))         
  OUTFIL BUILD=(1,450),INCLUDE=(16,8,CH,EQ,459,8,CH)                 
//*
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

Re: how to get the first two record when it was duplicated

Postby userTesting » Tue Apr 13, 2010 6:55 am

i try to run it give me syntax error below

  SORT FIELDS=(1,8,CH,A,16,8,CH,D),EQUALS                             
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(451:SEQNUM,8,ZD,RESTART=(1,8))), 
  IFTHEN=(WHEN=GROUP,BEGIN=(451,8,ZD,EQ,1),PUSH=(459:16,8))           
               $                                                     
SYNTAX ERROR                                                         
  OUTFIL BUILD=(1,450),INCLUDE=(16,8,CH,EQ,459,8,CH)
userTesting
 
Posts: 8
Joined: Fri Apr 09, 2010 12:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: how to get the first two record when it was duplicated

Postby userTesting » Tue Apr 13, 2010 7:21 am

the syntax is mark below the Group

and i had check my
ICE201I E RECORD TYPE IS F
userTesting
 
Posts: 8
Joined: Fri Apr 09, 2010 12:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: how to get the first two record when it was duplicated

Postby Frank Yaeger » Tue Apr 13, 2010 10:20 pm

You don't have the functional PTF that allows you to use WHEN=GROUP. Ask your System Programmer to install z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707 (Nov, 2009). That will get you all of the latest DFSORT functions. The PTF is free.
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: how to get the first two record when it was duplicated

Postby userTesting » Wed Apr 14, 2010 6:52 am

hi Frank Yaeger:

thank to your reply.
actually my company SP outsoucing to another comapny.
To install the latest version it may take many procedure and had difficulty to do so even is Free.we cannot install.
i had call on of the sp to ask for that.
what there answer me is we are currenly in z/os 1.7.
There had another upgrate services to z/os 1.9.
may i know what the diffrent between z/os 1.7 and z/os 1.9.
after there upgrate the level can the when=group be use?
userTesting
 
Posts: 8
Joined: Fri Apr 09, 2010 12:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: how to get the first two record when it was duplicated

Postby skolusu » Wed Apr 14, 2010 9:05 pm

usertesting,

You can install DFSORT V1R5 and apply the PTF UK51706 to it which will give you ALL the latest functions of DFSORT on z/OS 1.5 thru z/OS 1.9

You can install DFSORT V1R10 and apply the PTF UK51707 to it which will give you ALL the latest functions of DFSORT on z/OS 1.10 and higher

Here is an alternative solution for you assuming that you have the PTF installed which supports INREC on splice function

//STEP0100 EXEC PGM=ICETOOL   
//TOOLMSG  DD SYSOUT=*       
//DFSMSG   DD SYSOUT=*       
//IN       DD DSN=your input fb 450 byte file,DISP=SHR
//OUT      DD SYSOUT=*                                     
//TOOLIN   DD *                                             
  SPLICE FROM(IN) TO(OUT) ON(1,8,CH) WITHALL WITH(1,450) - 
  KEEPBASE KEEPNODUPS USING(CTL1)                           
//CTL1CNTL DD *                                             
  SORT FIELDS=(1,8,CH,A,16,8,CH,D),EQUALS                   
  INREC OVERLAY=(451:16,8)                                 
  OUTFIL BUILD=(1,450),INCLUDE=(16,8,CH,EQ,451,8,CH)       
//*
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


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post