[How to ] Find Max of the given records through Sort



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

[How to ] Find Max of the given records through Sort

Postby gauthamnagpur18 » Fri Jun 01, 2012 2:22 pm

Hi ,

I have a flat file which contains policyno(8) and due date(8) . It should return policy no with its max duedate .

Input :

00112345 20051201
00112345 20101205
00112345 20110120
00112345 20120112
00125692 20040801
00125692 20090912
00136759 20100509
00136759 20110406
00136759 20120101

O/p:

00112345 20120112
00125692 20090912
00136759 20120101


I know its possible through cobol .but is it possible through dfsort or icetool .Thanks in advance .
gauthamnagpur18
 
Posts: 93
Joined: Sat Oct 23, 2010 1:28 pm
Location: Chennai, India
Has thanked: 3 times
Been thanked: 3 times

Re: [How to ] Find Max of the given records through Sort

Postby olivermf » Fri Jun 01, 2012 2:52 pm

Hello gauthamnagpur18,

you can do this with two simple SORT-Steps.

First you sort your data ascending for policyno and descending for due date.
After that you delete entries with duplicate policyno.

//SORT1   EXEC PGM=SORT                           
//SYSOUT    DD SYSOUT=*                           
//SYSPRINT  DD SYSOUT=*                           
//SORTIN    DD *         
00112345 20051201
00112345 20101205
00112345 20110120
00112345 20120112
00125692 20040801
00125692 20090912
00136759 20100509
00136759 20110406
00136759 20120101
/*
//SORTOUT   DD DSN=&&TEMP,DISP=(NEW,CATLG,DELETE)
//SYSIN     DD *                                 
 SORT FIELDS=(1,8,A,10,8,D),FORMAT=BI             
/*                                               
//SORT2   EXEC PGM=SORT                           
//SYSOUT    DD SYSOUT=*                           
//SYSPRINT  DD SYSOUT=*                           
//SORTIN    DD DSN=&&TEMP,DISP=SHR               
//SORTOUT   DD SYSOUT=*
//SYSIN     DD *                                 
 SORT FIELDS=(1,8,A),FORMAT=BI                   
 SUM FIELDS=NONE                                 
/*                                               


As an alternative you could use ICETOOL and do this in one step.

Regards,

Oliver
olivermf
 
Posts: 53
Joined: Tue Feb 08, 2011 4:28 pm
Has thanked: 3 times
Been thanked: 0 time

Re: [How to ] Find Max of the given records through Sort

Postby gauthamnagpur18 » Fri Jun 01, 2012 2:58 pm

Thanks Oliver ..Its working !!
gauthamnagpur18
 
Posts: 93
Joined: Sat Oct 23, 2010 1:28 pm
Location: Chennai, India
Has thanked: 3 times
Been thanked: 3 times

Re: [How to ] Find Max of the given records through Sort

Postby olivermf » Fri Jun 01, 2012 3:26 pm

You're welcome!
Always happy to help ;-)
olivermf
 
Posts: 53
Joined: Tue Feb 08, 2011 4:28 pm
Has thanked: 3 times
Been thanked: 0 time

Re: [How to ] Find Max of the given records through Sort

Postby BillyBoyo » Fri Jun 01, 2012 3:27 pm

You should be able to do it in one pass of the data, if you look at using GROUP on OUTREC with OUTFIL INCLUDE/OMIT.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: [How to ] Find Max of the given records through Sort

Postby skolusu » Fri Jun 01, 2012 9:09 pm

gauthamnagpur18,

The easiest way to do this use the SELECT operator of ICETOOL and get the desired results. The following DFSORT/ICETOOL JCL will give you the desired results

//STEP0100 EXEC PGM=ICETOOL                           
//TOOLMSG  DD SYSOUT=*                                 
//DFSMSG   DD SYSOUT=*                                 
//IN       DD *                                       
----+----1----+----2----+----3----+----4----+----5----+
00112345 20051201                                     
00112345 20101205                                     
00112345 20110120                                     
00112345 20120112                                     
00125692 20040801                                     
00125692 20090912                                     
00136759 20100509                                     
00136759 20110406                                     
00136759 20120101                                     
//OUT      DD SYSOUT=*                                 
//TOOLIN   DD *                                       
  SELECT FROM(IN) TO(OUT) ON(01,8,CH) FIRST USING(CTL1)
//CTL1CNTL DD *                                       
  SORT FIELDS=(1,8,CH,A,10,8,CH,D)                     
//*


The output from this job is
00112345 20120112
00125692 20090912
00136759 20120101
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