IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Sort to omit records based on record count


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
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
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: 1702
Location: Australia

PostPosted: Fri Dec 05, 2008 3:44 am
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 Developer


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

PostPosted: Fri Dec 05, 2008 4:29 am
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: 1702
Location: Australia

PostPosted: Fri Dec 05, 2008 4:49 am
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 Developer


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

PostPosted: Fri Dec 05, 2008 4:55 am
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: 2481
Location: @my desk

PostPosted: Fri Dec 05, 2008 7:13 am
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
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 Developer


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

PostPosted: Fri Dec 05, 2008 10:57 pm
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:

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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts To get the count of rows for every 1 ... DB2 3
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts FINDREP - Only first record from give... DFSORT/ICETOOL 3
Search our Forums:

Back to Top