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

SORT card to concatenate the strings and totals at every set


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Wed Jun 13, 2012 7:32 pm
Reply with quote

Hi Experts,

Please help me to write the Sort card for the following requirement. We use SYNCSORT at our shop.
Attached is the screenshot of both the input and output files.




Instructions for the requirement are
Group the data based on the DATE, SEGMENT, Product and Type and then the field called "ID".
Merge the similar records in such a way that
a) Concatenate the data for the column "Description" and then sum up the "Qty".
b) Last record with Product "Onion" and Id "9999" did not club with that of the previous one as it has different ID numbers.
c) No need to worry about the Price
d) After that, each separate set should be followed by a Total "Total Sold" or "Total Packed" as highlighted in the screenshot
[img][/img]
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 Jun 13, 2012 7:38 pm
Reply with quote

If you use Syncsort, you should post in the JCL forum, not the DFSORT forum.
Back to top
View user's profile Send private message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Wed Jun 13, 2012 7:43 pm
Reply with quote

Hi Bill Woodger
Thanks for the alert.

Requesting moderators to move the thread to the respective section.
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 Jun 13, 2012 7:51 pm
Reply with quote

Have a look in your documentation for "reporting" features. HEADERn, TRAILERn etc. You should be able to find some examples by searching once you know the type of thing you want.
Back to top
View user's profile Send private message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Fri Jun 15, 2012 11:34 am
Reply with quote

Hi Bill Woodger,
From the reporting HEADERn, TRAILERn can be calculated and displayed. But the challenge is to concatenate the strings with similar key values.

As SUM is to sum up the numeric values of identity records, I want to do the same thing for character fields.

Please let me know how can we do it ?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Fri Jun 15, 2012 7:49 pm
Reply with quote

Hello,

Quote:
As SUM is to sum up the numeric values of identity records, I want to do the same thing for character fields.

How might character fields be SUMmed?

Is this shown in your posted "output"?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


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

PostPosted: Fri Jun 15, 2012 10:20 pm
Reply with quote

Have you looked at/tried DISPLAY? I've got a few sample reports done using this just as an educational thing.
Back to top
View user's profile Send private message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Sun Jun 17, 2012 3:22 pm
Reply with quote

Hi dick scherrer,

The attached image clearly shows that, How the character fields in column "Description" are concatenated / merged / summed up. Request you not to concentrate on my terminology.

Hi Nic Clouston,
I can use DISPLAY only in a cobol program. But I like to get this done using SORT.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Sun Jun 17, 2012 4:03 pm
Reply with quote

Quote:
Request you not to concentrate on my terminology.


You are not in the position to request anything from anybody icon_evil.gif

this is a professional forum, if You do not want to express <things> in such a way that people trying to help You do not have to waste time understanding Your terminology ....

then You better stick to chatting and nothing more!
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sun Jun 17, 2012 8:36 pm
Reply with quote

mkk157 wrote:
I can use DISPLAY only in a cobol program. But I like to get this done using SORT.


mkk157,
had you bothered to look at sort documentation,
you would find that DISPLAY is also a sort command.


Funny how those that are totally incompetent in IT,
are very familiar with the
'allow me to express myself, my way'
bullshit.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Mon Jun 18, 2012 1:17 am
Reply with quote

Hello,

Quote:
Request you not to concentrate on my terminology.
Nothng personal, but - Ahh, Umm, request denied (at least if you want help). You nave been here 6 years and should be able (by now) to use proper terminology. Communication is at least as importent as technology.

Quote:
The attached image clearly shows that,
Probably completely clear to you as you wrote it, but maybe not completely clear to everyone else - especially given the terminology used.

Pretty much everyone here wants to help, but the majority of the burden to get things done is with the poster.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


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

PostPosted: Mon Jun 18, 2012 1:58 am
Reply with quote

Probably fairly simple but as you have not bothered to explore the possibilities with sort or given any information that would allow one to help - all I can say is that it LOOKS as though it can easily be done by using the option that I mentioned before. By the way, how the heck do you think it could be done by the COBOL (or PL/1) DISPLAY statement?
Back to top
View user's profile Send private message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Mon Jun 18, 2012 1:46 pm
Reply with quote

Hi dick scherrer,
I am giving the full details of the requirement along with the input, output, copybook supported by necessary explanation.

Hi Nic Clouston,
I have read the manual for DISPLAY in SORT, But, couln't find any parameter to fulfil my requirement here.

Input
Code:
----+----1----+----2----+----3----+----4----+----5----+----6-
***************************** Top of Data *******************
20120601 FMCG    SOAP  SELL    CASH PURCHASE 2345   5 10   50
20120601 GROCERY ONION SELL    CREDIT CARD   3456  23 12  276
20120601 FRUITS  MANGO SHIPPED CREDIT CARD   1234 200 17 3400
20120601 FRUITS  MANGO SHIPPED CREDIT CARD   1234  15 17  255
20120601 FRUITS  MANGO SHIPPED DEBIT CARD    1234  15 17  255
20120601 GROCERY ONION SELL    CASH PURCHASE 3456  25 12  300
20120601 GROCERY ONION SHIPPED CASH PURCHASE 9999  50 12  600
20120601 FMCG    SOAP  SELL    CREDIT CARD   2345  25 10  250


Output
Code:
----+----1----+----2----+----3----+----4----+----5----+----6-
***************************** Top of Data *******************
20120601 FMCG    SOAP  SELL    CASH PURCHASE 2345  30 10  300
                               CREDIT CARD                   
                               TOTAL SOLD          30       
20120601 FRUITS  MANGO SHIPPED DEBIT CARD    1234 230 17 3910
                               CREDIT CARD                   
                               TOTAL SHIPPED      230       
20120601 GROCERY ONION SELL    CASH PURCHASE 3456  53 12  576
                               CREDIT CARD                   
20120601 GROCERY ONION SHIPPED CASH PURCHASE 9999  50 12  600
                               TOTAL SOLD          53       
                               TOTAL SHIPPED       50       
**************************** Bottom of Data *****************


copybook of the input file
Code:
01 WS-INFILE                   
   05 WS-DATE       PIC X(8).     Input file may have data related to more than one business day.
   05 FILLER        PIC X(1). 
   05 WS-SEGMENT    PIC X(7).     There are limited number of segments exists as of today, and the list will be added in near future.
   05 FILLER        PIC X(1). 
   05 WS-PRODUCT    PIC X(5).     There are limited number of products exists as of today, and the list will be added in near future.
   05 FILLER        PIC X(1). 
   05 WS-TYPE       PIC X(7).     Indicates type of dispatching the Products
   05 FILLER        PIC X(1). 
   05 WS-PAMENT     PIC X(13).    Indicates the mode of payment. Cash / Credit card / Debit card
   05 FILLER        PIC X(1). 
   05 WS-CUST-ID    PIC 9(4).     Customer Identification number
   05 FILLER        PIC X(1). 
   05 WS-QTY        PIC 9(3).     Number of products
   05 FILLER        PIC X(1).     
   05 WS-PRICE      PIC X(2).     Price of each product
   05 FILLER        PIC X(1). 
   05 WS-TOT-PRICE  PIC X(4).     Total price of the products purchased


* The output file should be in a sorted order of DATE, SEGMENT, Product, Type and ID.
* Group the data based on the DATE, SEGMENT, Product and Type and then the field called "ID".
* In case an identical record found (Similar DATE, SEGMENT and Type with ID), the respective, Qty and Tot Price fields should be summed up and the corresponding values in the column "Payment" should be merged and listed one after the other in different lines, here the duplicate values are to be removed in Payment field for any partcular set.
* Sample output file shows the sample input and output files.

Further details about the Output file
* Totals should be calculated after every set of DATE - SEGMANET - PRODUCT. for each value of TYPE exists in that set.
* Now, As control moves further, a new set of DATE - SEGMENT - PRODUCT - TYPE appears, the total should be calculated for number of items
* Record number 1 and 8 with identical DATE - SEGMENT - PRODUCT - TYPE - ID, were merged together and the 2 different types of payments CASH and CREDIT were listed in ascending order, "Qty" is summed up and "total price" is also calculated according to the summed up "Qty"
* Now, rest are the records 3, 4 and 5. All the three records are grouped together and the Payment values are listed with 2 different values such as DEBIT and CREDIT.
* Now, As control moves further, a new set of DATE - SEGMENT - PRODUCT - TYPE appears, the total should be calculated for number of items
* while arranging them in ascending order, Record numbers 2, 6 and 7 will come first . Out of these 3 records, 2 and 6 are identical and apply the rule above, and list the different payments CASH and CREDIT in ascending order for ID 3456. And the 7th record is non-identical as its ID is 9999. So to list it separately.
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 Jun 19, 2012 2:32 am
Reply with quote

You want a key which is concatenated from two fields. So make it.

OVERLAY=(newkeystart:keypart1start,kp1length,keypart2start,kp2length)

I'm short of time, but that's what I thought last time I saw this.
Back to top
View user's profile Send private message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Fri Jun 22, 2012 11:03 am
Reply with quote

Hi Bill Woodger,

Quote:
OVERLAY=(newkeystart:keypart1start,kp1length,keypart2start,kp2length)

OVERLAY also does not meet my requirement.

I wrote a cobol program to achieve the desited target.
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: Fri Jun 22, 2012 12:27 pm
Reply with quote

OK. Thanks for letting us know.

You needed a composite key, so you had to make it. I guess that is what you are now doing in your Cobol program.
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 -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts JCL sort to compare dates in two file... DFSORT/ICETOOL 2
No new posts Is this possible via sort (in one pass)? SYNCSORT 4
No new posts GDG generation name to GDG Base name ... DFSORT/ICETOOL 3
No new posts SORT on detail record, then repeat he... DFSORT/ICETOOL 3
No new posts FB to .CSV conversion using sort DFSORT/ICETOOL 7
Search our Forums:

Back to Top