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

Merging records and calculating TOTALS - SyncSort


IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
bodatrinadh

Active User


Joined: 05 Jan 2007
Posts: 101
Location: chennai (India)

PostPosted: Thu Mar 28, 2013 11:25 am
Reply with quote

Venkat,

I think you didn't check my code...
It is a syncsort solution and It is tested on SYNCSORT 1.4.0.1R

Thanks
-3nadh
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Mar 28, 2013 1:27 pm
Reply with quote

venkata.ravi,

Did you just get tired of me asking questions, so decided to ignore any more? If so, you'd best go with -3nadh's three-step solution (I assume you don't feel inclined to work on Sai's solution either). If your data is not in the order required, you could always add another SORT step.

The requirement for the outsorting may dictate the solution for whole thing (do you want it "dynamic", or does it only change rarely so can be done by code updates?). If you are still not keen to answer that, then it makes no nevermind to me.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Thu Mar 28, 2013 3:03 pm
Reply with quote

How about asking around your place of work for the manuals? If your place is licensed then it has manuals. They can probably order more as well if the manuals are 'lost'.
Back to top
View user's profile Send private message
venkata.ravi

New User


Joined: 09 May 2005
Posts: 30
Location: Hyderabad

PostPosted: Thu Mar 28, 2013 3:04 pm
Reply with quote

Hi trinadh,
Your code is fantastic. The resultant file format looks good to me except 2 minor changes.
1. While concatenating COL12 values, we are totaling COL11 values. Here, there is a possibility for the COL11 gets overflow and the actul value gets truncated. I would like to increase COL11 from 6 digits to 7 digits.
Ex:

Input
Code:
COL11 COL12
-----------
999991 ABC
023123 DEF


Output
Code:
COL11 COL12
-----------
023114 ABC, DEF      <<-- Here the value gets truncated


2. COL12 values are getting concatenated even though they are identical. In this case only COL11 values should be added together and to leave COL12 as it is.
Ex:

Input
Code:
COL11 COL12
-----------
001201 ABC
023123 ABC

024567 DEF
000023 DEF
000013 PQR

Output
Code:
COL11 COL12
-----------
024324 ABC

024603 DEF,PQR      <<--  DEF should not repeat here


Hi Bill,
I am not tired with your questions.
The outsorting order is fixed. Trinadh has covered it in the first step. It's fine. As Sai's solution is based on DFSORT, I couldn't test it with my inputs.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Mar 29, 2013 12:00 am
Reply with quote

bodatrinadh wrote:
Hi Skolusu,

I saw the comments which you replied to saiprasad...

But the input given by TS is a sorted one..And i assumed the same.

Yes, You are right. If the data is jumbled, my sort code won't work. It needs little modification.


bodatrinadh,

Apart from the SORTED vs UNSORTED output, there is one more issue with the job. As OP wanted the sort sequence to be "Required order of COL1 is ** EVALUATIONE, POPULAR, CONTRIBUTION, TOSS and others if any ** " You are only considering the first byte to determine the Sequence, if COL1 has the following data
Code:

E
EA
EB
EC
P1
P2
P3
....


You will have incorrect results. You need to validate all the 20 bytes and add the sorting indicator.

You are only considering E, P, C, T, G as the only valid alphabets that would get the sorting sequence. If you have data other than those values byte 178 would be a space and they would get sorted First before numbers 1,2,3,4,5

Your shop may have OPTION EQUALS as default, and you are getting the results in the right order you have , however if NOEQUALS is the default, then your test results will be unpredictable.
Back to top
View user's profile Send private message
venkata.ravi

New User


Joined: 09 May 2005
Posts: 30
Location: Hyderabad

PostPosted: Mon Apr 01, 2013 12:58 pm
Reply with quote

Hi Trinadh,

In addition to above 2 example, Here is another example, Code handling only 2 different values for COL12,
Input
Code:
COL11 COL12
-----------
012345 DEF
000033 ABC
000044 PQR


Output
Code:
COL11 COL12
-----------
012422 ABC,DEF      <<--  Count for all the records is coming but, the COL12 value (PQR) for 3rd identical record is missing. Can we make it dynamic, so that it can handle around 10 identical records.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Mon Apr 01, 2013 1:43 pm
Reply with quote

Code:
RQ#1: Sort the records in a user specific order for COL1.
Required order of COL1 is ** EVALUATIONE, POPULAR, CONTRIBUTION, TOSS and others if any **


RQ#2: Add COL11 together  if all other COL's (COL1 to COL10) are identical.

RQ#3: Add a new row for every set of COL1, COL2 and COL3 and give a name to the set as 'TOTAL : ' SUM(COL11)
Also add an empty row after every TOTAL. This is for better readability of the report.
The string 'TOTAL' starts at position 110 and the SUM(COUNT) starts from position at 121.


The above is very easy and can be done in one step.

INREC to prepare an additional field for the "outsort".

SORT on the "outsort" first, then the rest of the data to the amount.

Use OUTFIL with NODETAIL, with SECTIONS, with two sets of description for your two different control breaks. Use TOT to get the total.

That leaves the "concatenation" of COL 12. Work on the above whilst getting 100% clear on what you want for the "concatenation".

You now, "of course", want to "consolidate" the COL 12 of up to 10 records?

You haven't given much of an example of what might genuinely exist in the existing 50 bytes, where you say probably 1-25 bytes are used.

Can there be "embedded" blanks? Leading blanks? Or just trailing blanks?

Code:
A, B, C, D, E, A
A, B, C, D, E


With the former, you know what each record had, and you know that the total was consolidated from six records. With the latter, you have no clue either how many records, or what their original values were, other than the first element. Is that what you really want?

What about a COL 12 which is blank? How do you want that to be?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Mon Apr 01, 2013 1:50 pm
Reply with quote

Looking at -3nadh's code, it is only dealing with one instance of COL 12 for multiple records. If you understood the Control Cards you would know that, and not ask about what to change for that.

Bear in mind that unless your data already contains the entire groups (ie when sorted, no additional records will be added to a group, or to put it another ways, no groups are "consolidated" through a sort taking place) then this technique requires an additional SORT, which your client or your company are paying for the resources for.
Back to top
View user's profile Send private message
venkata.ravi

New User


Joined: 09 May 2005
Posts: 30
Location: Hyderabad

PostPosted: Mon Apr 01, 2013 2:41 pm
Reply with quote

Here are the COL12 looks like
Length - 50 bytes
It can never be blank.
It can never have leading spaces, but can have embedded and/or trailing spaces. Trailing spaces need to be truncated.
Similiar COL12 values need not to consider, but their corresponding COL11 should be summed up.

Quote:
Code:
A, B, C, D, E, A
A, B, C, D, E


Yes, COL11 values for the 6 records should be summed up, but the COL12 values should not be repeated.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Mon Apr 01, 2013 5:32 pm
Reply with quote

OK, let us know when you have the outsort and totals working, and there'll be help with the consolidation.
Back to top
View user's profile Send private message
venkata.ravi

New User


Joined: 09 May 2005
Posts: 30
Location: Hyderabad

PostPosted: Mon Apr 01, 2013 5:37 pm
Reply with quote

Hi Bill,
What do you mean by "outsort". Do you refer Req#1 as outsort ?
Quote:
RQ#1: Sort the records in a user specific order for COL1.
Required order of COL1 is ** EVALUATIONE, POPULAR, CONTRIBUTION, TOSS and others if any **
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Mon Apr 01, 2013 6:21 pm
Reply with quote

Yes. They are to be sorted "out of sequence", so an "outsort"...
Back to top
View user's profile Send private message
venkata.ravi

New User


Joined: 09 May 2005
Posts: 30
Location: Hyderabad

PostPosted: Tue Apr 02, 2013 10:39 am
Reply with quote

Hi Bill,
I'm able to "outsort" with the control card provided by trinadh. And the Total and sub-totals are also working fine with the sort provided by trinadh.

Only thing left is to handle COL12 values .
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Apr 02, 2013 11:45 am
Reply with quote

That solution has three SORTs in three steps.

It can be done in one SORT in one step.

Which do you think your client would prefer to be paying for?
Back to top
View user's profile Send private message
venkata.ravi

New User


Joined: 09 May 2005
Posts: 30
Location: Hyderabad

PostPosted: Tue Apr 02, 2013 2:01 pm
Reply with quote

Hi Bill,
If it can be done in single step, it would be really great. Can you please provide me that sort card ?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Apr 02, 2013 3:10 pm
Reply with quote

Did you read this? Or the earlier one?

With INREC, prepare a field appended to your FB which contains D, G, J, M for your four "outsorted" values respectively and which otherwise contains X.

The reason for the "gaps" in the letters is to allow for easy amendments.

Then SORT on the new field and then the rest of the key that you want, so that you get your "outsort" and then within that outsort you get the required order.

Then use OUTFIL with SECTIONS and TRAILER3.

Code:
  OUTFIL REMOVECC,NODETAIL,
           SECTIONS=(p,m,         start and length of your key for the TOTAL
                    TRAILER3=(c:C'TEXT YOU WANT',TOT=(p,m,ZD),/),
                    p,m,  start and length of your sub-total
                    TRAILER3=(p,m,   start and length of data before sub-tot
                             TOT=(p,m,ZD),
                             p,m  start and length of data before sub-tot))


I've not even really tried to get the syntax right, you've got to do some work yourself.

Then you just need the extended field for the summed values, and the "consolidation", but get the above working first.
Back to top
View user's profile Send private message
venkata.ravi

New User


Joined: 09 May 2005
Posts: 30
Location: Hyderabad

PostPosted: Tue Apr 02, 2013 4:36 pm
Reply with quote

Hi Bill,
Below are the 2 pieces of codes I have amended to the outsort using INREC in a single step.

This code returns sub totals for each row. But the actual row reflects empty, Actual data is missing but the sub totals for each totals are coming in order.
Code:
OUTFIL REMOVECC,NODETAIL,                                         
           SECTIONS=(1,121,                                       
                    TRAILER3=(122:C'TOTAL   : ',TOT=(122,6,ZD),/),
                    131,7)                                       



This piece of code returns an error message, " WER116A THE FOLLOWING H/T LINE IS GT LRECL:
First record after outsort displays here.

Code:
OUTFIL REMOVECC,NODETAIL,                                         
           SECTIONS=(1,121,                                       
                    TRAILER3=(122:C'TOTAL   : ',TOT=(122,6,ZD),/),
                    131,7,                                       
                    TRAILER3=(1,121,                             
                             TOT=(122,7,ZD),                     
                             1,121))                             
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Apr 02, 2013 5:17 pm
Reply with quote

You need two "totals" for the tasks. One is when the first three columns change (from memory) and here you want just a total followed by a blank line.

The second is that you want a summary value for the first 10 columns and you want that total to be "embedded" in the line with the 10 columns and the to-be-done-yet Column 12.

Code:
OUTFIL REMOVECC,NODETAIL,                                         
           SECTIONS=(1,121,                                       
                    TRAILER3=(122:C'TOTAL   : ',TOT=(122,6,ZD),/),
                    131,7)   


The above code does neither.

Try something like

Code:
OUTFIL REMOVECC,NODETAIL,                                         
           SECTIONS=(1,length-of-first-three-columns-on-output,
                    TRAILER3=(122:C'TOTAL   : ',TOT=(122,6,ZD),/))


That, on its own, should get you close to the totals you want for the first three columns. You can adjust the output for how you want it to look.

Code:
OUTFIL REMOVECC,NODETAIL,                                         
           SECTIONS=(1,121,
                    TRAILER3=(1,121,TOT=(122,6,ZD),128,10))


That, or close to it, on its own should give the summary line for the details.

They can be combined, something like this:
Code:
OUTFIL REMOVECC,NODETAIL,                                         
           SECTIONS=(1,length-of-first-three-columns-on-output,
                    TRAILER3=(122:C'TOTAL   : ',TOT=(122,6,ZD),/),
                1,121,
                    TRAILER3=(1,121,TOT=(122,6,ZD),128,length of remainder of line))


Remember you want the 6,ZD field to be "longer" to allow for the TOT. That plus the "consolidation" of COL 12 will be next, once you get the OUTFIL working and show all the Control Cards you have so far.

Again, I have not run the above myself, so you fix the bits that go wrong, rather than complain.
Back to top
View user's profile Send private message
venkata.ravi

New User


Joined: 09 May 2005
Posts: 30
Location: Hyderabad

PostPosted: Tue Apr 02, 2013 6:55 pm
Reply with quote

Hi Bill,

The below code is summing up totals
if identical records (COL1 to COL10 (1,121) and COL12 (128,50) but with different COL11's

Code:
OUTFIL REMOVECC,NODETAIL,                                 
           SECTIONS=(1,121,128,50,                         
                    TRAILER3=(1,121,TOT=(122,6,ZD),128,50))


With this sort card, the output file becomes 192 bytes and COL12 starts from 138
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Apr 02, 2013 7:39 pm
Reply with quote

Are you saying this is some type of problem? If yes, be clear about it. Look at the size of the thread. This should not take so much effort.

You didn't previously mention you wanted to "total" if COL1-10 were equal AND COL12 was equal. You specified completely different processing for COL12.

Is this a change?

When I wrote my code, I used an EDIT for the TOT there. Have you tried that?

Remember, last thing you said was you wanted the (up to) 10 values for COL12 consolidated on the one line, which will be an extension of the line by 450 bytes.

Also, again, remember that you want an extra position for COL11, so that the total is less likely to overflow. The start position of COL12 (consolidated or otherwise) will have to change by one to be able to do that.
Back to top
View user's profile Send private message
venkata.ravi

New User


Joined: 09 May 2005
Posts: 30
Location: Hyderabad

PostPosted: Wed Apr 03, 2013 2:48 pm
Reply with quote

Hi Bill,

It's not the new issue at all.

COL11 should be summed up if COL1 to COL10 are equal, Before computing the totals, we need to pair up all the different COL12 values. With the above sort card, Totals can be computed if COL1 to COL10 and COL12 values are equal.


Code:
OUTFIL REMOVECC,NODETAIL,                                 
           SECTIONS=(1,121,128,50,                         
                    TRAILER3=(1,121,TOT=(122,6,ZD),128,50))   
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Apr 03, 2013 3:28 pm
Reply with quote

Code:
COL1-10 somevalue
COL11   amount1
COL12   A
COL1-10 samevalueasabove
COL11   amount2
COL12   B
COL1-10 samevalueasabove
COL11   amount3
COL12   A


With your code the above will get three output records with no "summing", ie your output will be equal to your input..

What you said you wanted, it seems to me, is one "summed" record of COL1-20 with A,B in COL12.

So, what do you want in the above case?
Back to top
View user's profile Send private message
venkata.ravi

New User


Joined: 09 May 2005
Posts: 30
Location: Hyderabad

PostPosted: Wed Apr 03, 2013 3:56 pm
Reply with quote

Hi Bill,
with my sort card, and with your criteria, it will result in only 2 records. which is expected.
Code:
Input taken
--+----2----+----3---
*********************
 20130306000150ABCDE
 20130306000250B     
 20130306000350ABCDE
*********************

Output:
--+----2----+----3----+----4--
******************************
 20130306            500 ABCDE
 20130306            250 B   
******************************


COL11 converts to ZD. 150+350 summed up and becomes 500 for COL12 ABCDE, as they are same.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Apr 03, 2013 4:08 pm
Reply with quote

Presumably you are sorting on COL12 then, which you didn't mention.

Presumably also you no longer need the "consolidation" of COL12?

That's good.

So you just need to extend the COL11 for the TOT so it doesn't overflow so easily. I'd append C'0',startCOL11,lengthCOL11 to the end of the record and TOT on that.
Back to top
View user's profile Send private message
venkata.ravi

New User


Joined: 09 May 2005
Posts: 30
Location: Hyderabad

PostPosted: Wed Apr 03, 2013 4:48 pm
Reply with quote

Hi Bill,

I'm still working on consolidation of COL12 values, which is the last step in the requirement.
On the other hand, I have just sent the sample reports to users and requested their feedback on the output file (this does not cover consolidation of COL12). I'm waiting for their response on this.
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 -> SYNCSORT Goto page 1, 2  Next

 


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 Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
No new posts JCL sortcard to print only the records DFSORT/ICETOOL 11
Search our Forums:

Back to Top