Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Select required records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Select required records
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: 1187
Location: Bangalore,India

PostPosted: Tue Dec 19, 2006 4:59 pm    Post subject:
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    Post subject:
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: 1187
Location: Bangalore,India

PostPosted: Tue Dec 19, 2006 6:11 pm    Post subject:
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    Post subject:
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 Moderator


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

PostPosted: Tue Dec 19, 2006 10:04 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us