Joined: 05 Oct 2005 Posts: 5 Topics: 3 Location: Near Detroit
Posted: Wed Dec 12, 2007 4:57 pm Post subject: Select by priority number in 1 step
Is it possible to select 1 record from duplicates based on a priority field in only 1 step? The file contains a few million records so we would like to pass it through the fewest number of steps.
Specs:
Select the highest priority of each ID record set.
The ID and priority fields may be duplicated.
Not all priority values will be present.
IDs are listed in no set order.
Priorities in no set order.
ID and Priority fields are not adjacent.
Example:
Input
Code:
02 xx 1
01 xx 3
02 xx 2
01 xx 2
02 xx 1
02 xx 3
02 xx 2
01 xx 2
Output
Code:
01 xx 2
02 xx 1
Using DFSORT and Sum Fields leaves us with multiple priority records for each ID.
Using ICETOOL and Select ON(ID) and ON(PRIORITY) also leaves us with multiple priority records.
If we sort the output by ID with Sum Fields=None and Equals, it will give us the required output, but we may still be sorting a couple million records.
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Wed Dec 12, 2007 5:09 pm Post subject:
You can use a DFSORT/ICETOOL job like this to do what you asked for:
Code:
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD *
02 xx 1
01 xx 3
02 xx 2
01 xx 2
02 xx 1
02 xx 3
02 xx 2
01 xx 2
/*
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,2,CH) FIRST USING(CTL1)
/*
//CTL1CNTL DD *
SORT FIELDS=(1,2,CH,A,7,1,CH,A)
/*
_________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
