View previous topic :: View next topic
|
Author |
Message |
Aneesh
New User
Joined: 30 Sep 2006 Posts: 60
|
|
|
|
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 |
|
|
gcicchet
Senior Member
Joined: 28 Jul 2006 Posts: 1702 Location: Australia
|
|
|
|
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 |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
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 |
|
|
gcicchet
Senior Member
Joined: 28 Jul 2006 Posts: 1702 Location: Australia
|
|
|
|
Hi Frank,
to ensure records are kept in the same order as input, otherwise I agree your solution is simpler
Gerry |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
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 |
|
|
Aneesh
New User
Joined: 30 Sep 2006 Posts: 60
|
|
|
|
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 |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
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 |
|
|
Aneesh
New User
Joined: 30 Sep 2006 Posts: 60
|
|
|
|
Thank you. |
|
Back to top |
|
|
|