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

Select required records


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
siddheart22

New User


Joined: 09 Oct 2006
Posts: 19
Location: Toronto

PostPosted: Tue Dec 19, 2006 4:40 pm
Reply with quote

Hi,

I want to achieve the following using SORT card, please suggest the optimal way/s of doing this.

My data looks as below:
Code:

COL-A       COL-B  COL-C      COL-D
0023000334   USD   2           0023000334
9876201831   GEN   7           0873073159
9300000024   CAD   2           9300000024
9315024217   CAD   6           9300000024
9315023755   CAD   8           9300000024
9300018802   CAD   2           9300018802
9321900780   CAD   6           9300018802
9300655991   CAD   6           9300018802
9321113079   USD   8           9300018802
4391302155   USD   8           9300018802
9300018810   CAD   2           9300018810
9300018828   CAD   2           9300018828
9300018836   CAD   2           9300018836
9325964048   CAD   6           9300018836
9325963982   CAD   8           9300018836


I want the output such that, all duplicates in COL-D are removed and only the one corresponding to the highest value in COL-C for a given value in COL-D is retained.

Example:
For COL-D = 9300000024
Only one record should be present in output where COL-C = 8

Note: It could be possible that there are two records for the same value in COL-D where the highest value in COL-C is same. In such a case, I want both the records in the output.

Any help is appreciated.
Thanks,
Sid.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India

PostPosted: Tue Dec 19, 2006 4:59 pm
Reply with quote

Hi Sid,

We can acheive it in 2 step

First step sort card

Code:
SORT FIELDS=(15,10,ZD,A,14,1,ZD,D)



Second step sort card

Code:
OPTION EQUALS
SORT FIELDS=(15,10,ZD,A)
SUM FIELDS=NONE


Quote:
Note: It could be possible that there are two records for the same value in COL-D where the highest value in COL-C is same. In such a case, I want both the records in the output.


I am just try to give u solution . Frank can provide better solution.
Back to top
View user's profile Send private message
siddheart22

New User


Joined: 09 Oct 2006
Posts: 19
Location: Toronto

PostPosted: Tue Dec 19, 2006 6:04 pm
Reply with quote

Hi,

Forgot to mention earlier, that my input file is sorted on COL-D in ascending order.

Using a sort descending on COL-C and de-duping, partially solves my problem.

Is there any way in SORT where I can achieve -
Quote:

Note: It could be possible that there are two records for the same value in COL-D where the highest value in COL-C is same. In such a case, I want both the records in the output.


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

Moderator


Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India

PostPosted: Tue Dec 19, 2006 6:11 pm
Reply with quote

Hi Sid,

In that case change the first step sort card to

Code:
SORT FIELDS=(15,10,ZD,D,14,1,ZD,D)


Regarding this as i said earlier frank can provide solution

Quote:
Note: It could be possible that there are two records for the same value in COL-D where the highest value in COL-C is same. In such a case, I want both the records in the output.
Back to top
View user's profile Send private message
siddheart22

New User


Joined: 09 Oct 2006
Posts: 19
Location: Toronto

PostPosted: Tue Dec 19, 2006 9:07 pm
Reply with quote

Hi Frank,

If possible, please suggest how can this be achieved using SORT?

Thanks,
Sid.
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: Tue Dec 19, 2006 10:04 pm
Reply with quote

Sid,

Keeping dup records with the highest COL-C makes this a bit tricky, but here's a DFSORT/ICETOOL job that will do what you asked for. I assumed your input file has RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes.

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD *
0023000334      USD     2               0023000334
9876201831      GEN     7               0873073159
9300000024      CAD     2               9300000024
9315024217      CAD     6               9300000024
9315023755      CAD     8               9300000024
9300018802      CAD     2               9300018802
9321900780      CAD     6               9300018802
9300655991      CAD     6               9300018802
9321113079      USD     8               9300018802
4391302155      USD     8               9300018802
9300018810      CAD     2               9300018810
9300018828      CAD     2               9300018828
9300018836      CAD     2               9300018836
9325964048      CAD     6               9300018836
9325963982      CAD     8               9300018836
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN) TO(T1) ON(41,10,CH) FIRST USING(CTL1)
COPY FROM(IN) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(41,10,CH) ON(25,1,CH) -
  WITHALL WITH(1,81) USING(CTL3)
/*
//CTL1CNTL DD *
  SORT FIELDS=(41,10,CH,A,25,1,CH,D)
  OUTFIL FNAMES=T1,OVERLAY=(81:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(81,2,CH,EQ,C'VB'),
    BUILD=(1,80)
/*


OUT has:

Code:

0023000334      USD     2               0023000334       
9876201831      GEN     7               0873073159       
9315023755      CAD     8               9300000024       
9321113079      USD     8               9300018802       
4391302155      USD     8               9300018802       
9300018810      CAD     2               9300018810       
9300018828      CAD     2               9300018828       
9325963982      CAD     8               9300018836       
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 Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
Search our Forums:

Back to Top