HERE IN THE ABOVE EXAMPLE THE FIRST 2 RECORDS ARE SAME WHEN WE COMPARE THE FIRST TWO FIELDS,,,I.E WE HAVE A DUPLICATE. NOW WHEN SUCH CONDITION HAPPENS I WANT TO DELETE THE RECORD WITH B. HOW CAN I ACHIEVE THE SAME. CAN SOMEONE HELP ME.....
SELECT FROM(IN) TO(OUT) ON(1,4,CH) ON(6,4,CH) FIRST USING(CTL1)
This is an ICETOOL SELECT operator:
FROM(IN) - defines the input file
TO(OUT) - defines the output file
ON(1,4,CH) ON(6,4,CH) - defines the ON fields as in positions 1-4 and 6-9. We check for dups and nondups on these two fields.
FIRST - keeps the first record with dup on fields (e.g. for 1111 2007 A and 1111 2007 B, it keeps 1111 2007 A) as well as unique records (e.g. 2222 2008 A).
USING(CTL1) - tells DFSORT to use the DFSORT control statements in CTL1CNTL.
which sorts on the field in positions 1-4 (first ON field), the field in positions 6-9 (the second ON field) and the field in position 11 (not an ON field). The SORT is performed before SELECT processing. By sorting on the field in position 11 but not using it as an ON field, we ensure that SELECT sees the A record before the B record for each dup pair (for the two on fields) and keeps the A record as the FIRST record.