|
|
| Author |
Message |
ambilileela
Member
Joined: 06 Jun 2006
Posts: 11
|
 Posted: Tue Jun 06, 2006 2:16 pm Post subject: Help required for a sort step. |
 |
|
Hi ,
I have a small query regarding usage of SORT card utility.
Situation:
There say a table(A) with the following data
Table A
| Code: |
a1 a4 amts1 amts2 amts3 adate
james 30 40 40 40 24-jan-2005
james 30 13 23 23 25-jan-2005
james 30 13 23 23 26-jan-2005
james 30 13 23 23 27-jan-2005
james 30 13 23 23 28-jan-2005
...........................................
|
arnd 15 records for same column a1 value 'james'.
Similarly, i may have a no: of records with same value in column a1.
Now, the pbm is that
1) I need to check for the total no: of records for a given column 'a1', if the count is greater than 10 (say it is 'y'),then i need to merge the first (y-9) records as a single record, and then the rest will follow sequentially.
Let me explain with an example.
say i have 15 records with a1 value as 'James', then i need to merge first (15-9) records into a single record, with the date field(adate) pointing to the latest date of the six records. That means , at any given point of time
the record count for each unique column a1 value should not be more than 10 records.
And I have to incorporate this logic in the JCL Only .. meaning no COBOL code change.
Thanks,
Ambili. |
|
| Back to top |
|
|
|
Frank Yaeger
Moderator
Joined: 18 Feb 2006
Posts: 487
Location: San Jose, CA
|
| Posted: Tue Jun 06, 2006 10:18 pm Post subject: |
|
|
Some questions:
Do you only have one key (e.g. "James") in the file, or can you have multiple keys (e.g. "James", "Richard", "Carl").
What is the RECFM and LRECL of the input file?
Are the adate values already in ascending order as shown in your example?
Can you please show a better example of the input records (with more than 10 instances of the key and multiple keys if that's what you have) and what you expect for output.
_________________
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort/ |
|
| Back to top |
|
|
|
ambilileela
Member
Joined: 06 Jun 2006
Posts: 11
|
| Posted: Tue Jun 13, 2006 3:35 pm Post subject: |
|
|
Hi ,
Let me explain you in a more elaborate way:
Let the table be having the following columns:
Type Sub-Type Credit-Amt Debit-Amt
Type Sub-Type Date-field Credit-Amt Debit-Amt
Cereals Wheat 3/14/2001 15 17
Cereals Wheat 3/14/2001 24 22
Cereals Wheat 3/14/2001 2 4
Cereals Wheat 3/14/2001 3 3
Cereals Wheat 3/14/2001 3 3
Cereals Wheat 3/14/2001 10 9
Cereals Wheat 3/24/2001 17 17
Cereals Wheat 3/14/2001 22 22
Cereals Wheat 3/22/2001 4 4
Cereals Wheat 3/23/2001 3 3
Cereals Wheat 3/14/2001 3 3
Cereals Wheat 3/25/2001 9 9
Cereals Maize 3/26/2001 17 17
Cereals Maize 3/27/2001 22 22
Cereals Maize 3/14/2001 4 4
Cereals Maize 3/14/2001 3 3
Cereals Maize 3/14/2001 3 3
Cereals Maize 3/14/2001 9 9
Cereals Maize 3/14/2001 17 17
Cereals Maize 3/14/2001 22 22
Cereals Maize 3/24/2001 4 4
Cereals Maize 3/14/2001 3 3
Cereals Maize 3/19/2001 3 3
Cereals Maize 3/20/2001 9 9
Cereals Maize 3/21/2001 17 17
Cereals Maize 3/22/2001 22 22
Cereals Rice 4/9/2001 4 4
Cereals Rice 4/10/2001 3 3
Cereals Rice 3/14/2001 3 3
Cereals Rice 3/14/2001 9 9
Cereals Rice 3/14/2001 17 17
Cereals Rice 3/14/2001 22 22
Cereals Rice 3/14/2001 4 4
Cereals Rice 3/14/2001 3 3
Cereals Rice 3/24/2001 3 3
Cereals Rice 3/14/2001 9 9
Cereals Rice 3/17/2001 17 17
Cereals Rice 3/18/2001 22 22
Juices Beverages 3/19/2001 4 4
Juices Beverages 3/20/2001 3 3
Juices Beverages 4/23/2001 3 3
Juices Beverages 4/24/2001 9 9
Juices Beverages 4/25/2001 17 17
Juices Beverages 4/26/2001 22 22
Juices Beverages 4/27/2001 4 4
Juices Beverages 4/28/2001 3 3
Now I have sorted the records based on the Type and Date-field, and summed the
Credit-Amt and Debit-Amt fields thro a SORT step.
So say for a given Type Cereal and Date field '3/14/2001', it will sum all amts.
Now comes my problem.
given the above example , my file records after sum/sort will look like this:
Type Date-field Credit-Amt Debit-Amt
Cereals 3/14/2001 225 225
Cereals 3/17/2001 3 3
Cereals 3/19/2001 26 26
Cereals 3/20/2001 20 20
Cereals 3/21/2001 9 9
Cereals 3/22/2001 6 6
Cereals 3/23/2001 4 4
Cereals 3/24/2001 49 48
Cereals 3/26/2001 3 3
Cereals 3/27/2001 2 4
Cereals 4/9/2001 24 22
Cereals 4/10/2001 15 17
Cereals 4/23/2001 3 3
Juices 3/19/2001 4 4
Juices 3/20/2001 3 3
Juices 4/23/2001 3 3
Juices 4/24/2001 9 9
Juices 4/25/2001 17 17
Juices 4/26/2001 22 22
Juices 4/27/2001 4 4
Juices 4/28/2001 3 3
Now, for a given Type, there should not be more than 10 records, but as you see
that Type 'Cereal' is having 13 no: of records, so what i intend to do is that
take the count of records for a given Type (in this case '13'), then
merge the first (13-9=4) records into the first record by summing up the amts, and taking
the latest date value among the first(13-9=4) records.
And then the rest of the records(13-4) reocrds can be sequentially added , so that I will
have just (9+1=10) records at any given time.
Taking the above example, we will have the final output as:
Type Date-field Credit-Amt Debit-Amt
Cereals 3/20/2001 274 274 (After merging of first 4 records)
Cereals 3/21/2001 9 9
Cereals 3/22/2001 6 6
Cereals 3/23/2001 4 4
Cereals 3/24/2001 49 48
Cereals 3/26/2001 3 3
Cereals 3/27/2001 2 4
Cereals 4/9/2001 24 22
Cereals 4/10/2001 15 17
Cereals 4/23/2001 3 3
Juices 3/19/2001 4 4
Juices 3/20/2001 3 3
Juices 4/23/2001 3 3
Juices 4/24/2001 9 9
Juices 4/25/2001 17 17
Juices 4/26/2001 22 22
Juices 4/27/2001 4 4
Juices 4/28/2001 3 3
Hence, total of 10 records(max) for a given Type.
I hope this example has cleared your queries.
Do let me know for any solution reg this.
Btw, RECFM-FB
LRECL-38 of the input file.
Thanks in advance,
Ambili |
|
| Back to top |
|
|
|
Frank Yaeger
Moderator
Joined: 18 Feb 2006
Posts: 487
Location: San Jose, CA
|
| Posted: Wed Jun 14, 2006 12:11 am Post subject: |
|
|
Ok, now I understand what you want to do. It's rather tricky, but the DFSORT/ICETOOL job below will give you what you asked for. Since you didn't give me the starting position, length and format of each of your fields, I assumed the following:
| Code: |
Type = 1,12,CH
Date-field = 13,10,CH
Credit-Amt = 23,5,ZD
Debit-Amt = 28,5,ZD
|
I also assumed that your input records were already in order as shown.
So the input records for Cereals look like this:
| Code: |
Cereals 03/14/20010022500225
Cereals 03/17/20010000300003
Cereals 03/19/20010002600026
Cereals 03/20/20010002000020
Cereals 03/21/20010000900009
Cereals 03/22/20010000600006
Cereals 03/23/20010000400004
Cereals 03/24/20010004900048
Cereals 03/26/20010000300003
Cereals 03/27/20010000200004
Cereals 04/09/20010002400022
Cereals 04/10/20010001500017
Cereals 04/23/20010000300003
|
and the output records for Cereals look like this:
| Code: |
Cereals 3/20/20010027400274
Cereals 3/21/20010000900009
Cereals 3/22/20010000600006
Cereals 3/23/20010000400004
Cereals 3/24/20010004900048
Cereals 3/26/20010000300003
Cereals 3/27/20010000200004
Cereals 4/09/20010002400022
Cereals 4/10/20010001500017
Cereals 4/23/20010000300003
|
You can change the job appropriately if my assumptions are incorrect.
| Code: |
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DSN=... input file (FB/38)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//CON DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS)
// DD DSN=*.T2,VOL=REF=*.T2,DISP=(OLD,PASS)
//T3 DD DSN=&&T3,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=... output file (FB/38)
//TOOLIN DD *
COPY FROM(IN) USING(CTL1)
SPLICE FROM(CON) TO(T3) ON(1,12,CH) -
WITHALL WITH(1,52) USING(CTL2)
COPY FROM(T3) USING(CTL3)
/*
//CTL1CNTL DD *
OUTFIL FNAMES=T1,REMOVECC,NODETAIL,
BUILD=(54X),
SECTIONS=(1,12,
TRAILER3=(1,12,53:COUNT-9=(TO=ZD,LENGTH=2)))
OUTFIL FNAMES=T2,
OVERLAY=(39:1,12,51:SEQNUM,2,ZD,RESTART=(1,12),2X)
/*
//CTL2CNTL DD *
OUTFIL FNAMES=T3,
IFTHEN=(WHEN=(51,2,ZD,LE,53,2,ZD),OVERLAY=(51:C'00'))
/*
//CTL3CNTL DD *
OUTFIL FNAMES=OUT,REMOVECC,NODETAIL,
BUILD=(1,38),
SECTIONS=(39,14,
TRAILER3=(1,22,
TOT=(23,5,ZD,TO=ZD,LENGTH=5),
TOT=(28,5,ZD,TO=ZD,LENGTH=5)))
/*
|
_________________
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort/ |
|
| Back to top |
|
|
|
ambilileela
Member
Joined: 06 Jun 2006
Posts: 11
|
| Posted: Tue Jul 04, 2006 5:30 pm Post subject: |
|
|
Hi,
Thnx for the reply and the solution. I havent tried that solution , but then I wanted to know where does this code takes care of having maximum of those (count-9) records date field?
Do let me know at the earliest and Oblige.
Thanks in advance.
Ambili |
|
| Back to top |
|
|
|
Frank Yaeger
Moderator
Joined: 18 Feb 2006
Posts: 487
Location: San Jose, CA
|
| Posted: Tue Jul 04, 2006 9:00 pm Post subject: |
|
|
| Quote: | | wanted to know where does this code takes care of having maximum of those (count-9) records date field? |
Well, it's rather tricky. Let's just take the Cereals records for an example.
T1 will have:
T2 will have:
| Code: |
Cereals 3/14/2001 225 225 Cereals 01
Cereals 3/17/2001 3 3 Cereals 02
Cereals 3/19/2001 26 26 Cereals 03
Cereals 3/20/2001 20 20 Cereals 04
Cereals 3/21/2001 9 9 Cereals 05
Cereals 3/22/2001 6 6 Cereals 06
Cereals 3/23/2001 4 4 Cereals 07
Cereals 3/24/2001 49 48 Cereals 08
Cereals 3/26/2001 3 3 Cereals 09
Cereals 3/27/2001 2 4 Cereals 10
Cereals 4/09/2001 24 22 Cereals 11
Cereals 4/10/2001 15 17 Cereals 12
Cereals 4/23/2001 3 3 Cereals 13
|
We SPLICE the 04 from T1 into the T2 records to get:
| Code: |
Cereals 3/14/2001 225 225 Cereals 0104
Cereals 3/17/2001 3 3 Cereals 0204
Cereals 3/19/2001 26 26 Cereals 0304
Cereals 3/20/2001 20 20 Cereals 0404
Cereals 3/21/2001 9 9 Cereals 0504
Cereals 3/22/2001 6 6 Cereals 0604
Cereals 3/23/2001 4 4 Cereals 0704
Cereals 3/24/2001 49 48 Cereals 0804
Cereals 3/26/2001 3 3 Cereals 0904
Cereals 3/27/2001 2 4 Cereals 1004
Cereals 4/09/2001 24 22 Cereals 1104
Cereals 4/10/2001 15 17 Cereals 1204
Cereals 4/23/2001 3 3 Cereals 1304
|
We use:
IFTHEN=(WHEN=(51,2,ZD,LE,53,2,ZD),OVERLAY=(51:C'00'))
to get:
| Code: |
Cereals 3/14/2001 225 225 Cereals 0004
Cereals 3/17/2001 3 3 Cereals 0004
Cereals 3/19/2001 26 26 Cereals 0004
Cereals 3/20/2001 20 20 Cereals 0004
Cereals 3/21/2001 9 9 Cereals 0504
Cereals 3/22/2001 6 6 Cereals 0604
Cereals 3/23/2001 4 4 Cereals 0704
Cereals 3/24/2001 49 48 Cereals 0804
Cereals 3/26/2001 3 3 Cereals 0904
Cereals 3/27/2001 2 4 Cereals 1004
Cereals 4/09/2001 24 22 Cereals 1104
Cereals 4/10/2001 15 17 Cereals 1204
Cereals 4/23/2001 3 3 Cereals 1304
|
Then we use SECTIONS, NODETAIL and TRAILER3 to get only the last of the four '0004' duplicate records along with the unique '0504'-'1304' records. That gives us the last 10 records you wanted.
_________________
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort/ |
|
| Back to top |
|
|
|
|
|
|
FAQ 
Search 
Register
Profile 
Log in to check your private messages 
Log in 
