Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Sort to omit records based on record count

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Aneesh

New User


Joined: 30 Sep 2006
Posts: 60

PostPosted: Fri Dec 05, 2008 3:09 am    Post subject: Sort to omit records based on record count
Reply with quote

Hi,

I have a file (FB length 80) that has a numeric key field from position 1-4. The requirement is to omit all records for a particular key value if the total count of records for that key is less than a given value. (say 5)

Sample input
0001 1
0001 2
0001 3
0002 1
0002 2
0002 3
0002 4
0002 5
0002 6
0003 1
0004 1
0004 2

Output
0002 1
0002 2
0002 3
0002 4
0002 5
0002 6
Only the key value 2 has more than 5 records. The other key value records have to be omitted.

Can this be done using sort?

Thanks,
Aneesh.
Back to top
View user's profile Send private message

gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1703
Location: Australia

PostPosted: Fri Dec 05, 2008 3:44 am    Post subject:
Reply with quote

Hi,

you can try this
Code:
//S1       EXEC  PGM=ICETOOL                                           
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//IN       DD * 
0001 1       
0001 2       
0001 3       
0002 1       
0002 2       
0002 3       
0002 4       
0002 5       
0002 6       
0003 1       
0004 1       
0004 2                                                           
/*                                                                     
//T1       DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)       
//T2       DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)       
//OUT      DD SYSOUT=*                                                 
//TOOLIN   DD *                                                         
COPY FROM(IN) TO(T1) USING(CTL1)                                       
SELECT FROM(T1) TO(T2) ON(1,4,CH) HIGHER(5)                             
SORT FROM(T2) TO(OUT) USING(CTL2)                                       
/*                                                                     
//CTL1CNTL DD *                                                         
  INREC OVERLAY=(81:SEQNUM,8,ZD)                                       
/*                                                                     
//CTL2CNTL DD *                                                         
  SORT FIELDS=(81,8,ZD,A)                                               
  OUTREC BUILD=(1,80)                                                   
/*                                                                     



Gerry
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Dec 05, 2008 4:29 am    Post subject:
Reply with quote

Gerry,

You've complicated the solution unnecessarily (not sure why). We only need one pass, not three.

Aneesh,

Here's a DFSORT/ICETOOL job that will only keep the records where the key has more than 5 occurrences. I've added more input records for a better test.

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN DD *
0001 1
0001 2
0001 3
0002 1
0002 2
0002 3
0002 4
0002 5
0002 6
0003 1
0004 1
0004 2
0005 1
0005 2
0005 3
0005 4
0005 5
0006 1
0006 2
0006 3
0006 4
0006 5
0006 6
0006 7
0007 1
0007 2
0007 3
0007 4
/*
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,4,ZD) HIGHER(5)
/*


OUT will have these records:

Code:

0002 1   
0002 2   
0002 3   
0002 4   
0002 5   
0002 6   
0006 1   
0006 2   
0006 3   
0006 4   
0006 5   
0006 6   
0006 7   
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1703
Location: Australia

PostPosted: Fri Dec 05, 2008 4:49 am    Post subject:
Reply with quote

Hi Frank,

to ensure records are kept in the same order as input, otherwise I agree your solution is simpler


Gerry
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Dec 05, 2008 4:55 am    Post subject:
Reply with quote

So you're assuming that the output records should not be in sorted order by the key even though the example shows the output records sorted by the key. I guess I always assume things are as they seem unless told otherwise.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2276
Location: @my desk

PostPosted: Fri Dec 05, 2008 7:13 am    Post subject:
Reply with quote

Quote:
So you're assuming that the output records should not be in sorted order by the key even though the example shows the output records sorted by the key
Frank,

I think Gerry was trying to retain the order of equally keyed records rather than sorting it by key.
Here I have a question- Will the order of equally keyed records be the same after the above mentioned 'SELECT'?
Back to top
View user's profile Send private message
Aneesh

New User


Joined: 30 Sep 2006
Posts: 60

PostPosted: Fri Dec 05, 2008 7:39 pm    Post subject: Reply to: Sort to omit records based on record count
Reply with quote

Thank you guys. Both solutions work fine for me - the record sort order in the output file was immaterial for my purposes. Thanks again.

One question - what is the upper limit on the value that can be provided in the HIGHER clause?

Thanks,
Aneesh.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Dec 05, 2008 10:57 pm    Post subject:
Reply with quote

Quote:
Will the order of equally keyed records be the same after the above mentioned 'SELECT'?


Yes, because SELECT automatically sets EQUALS in effect. Gerry's solution would only be needed if the output records needed to be in their original order rather than in sorted order, eg. if the input records had keys of 2, 3, 1 and the output should have keys of 2, 3, 1 rather than 1, 2, 3.

Quote:
Both solutions work fine for me - the record sort order in the output file was immaterial for my purposes.


Then you should use my one pass SELECT solution since it is more efficient than the three pass solution.

Quote:
what is the upper limit on the value that can be provided in the HIGHER clause?


99

For complete details on the DFSORT's SELECT operator, see:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA30/6.11?DT=20080528171007
Back to top
View user's profile Send private message
Aneesh

New User


Joined: 30 Sep 2006
Posts: 60

PostPosted: Sat Dec 06, 2008 6:18 am    Post subject: Reply to: Sort to omit records based on record count
Reply with quote

Thank you.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to change 'K' or 'M' use Sort vice_versa DFSORT/ICETOOL 5 Thu May 18, 2017 7:11 am
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
This topic is locked: you cannot edit posts or make replies. SORT trick needed bshkris SYNCSORT 6 Tue May 02, 2017 4:35 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us