Is it possible to have GROUP BY Functionality in SORT



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Is it possible to have GROUP BY Functionality in SORT

Postby seethap » Wed Jul 27, 2016 9:23 pm

Hi,

Just want to know that whether GROUP BY functionality is available in SORT.

Assume that Input File has only two fields. They are NAME (10 bytes) and amount (5 bytes).
Input File:
SURESH 10000
RAMESH 25000
SURESH 15000
SURESH 20000
RAMESH 10000

Output file should have Name, total amount, no of records.

RAMESH 35000 2
SURESH 45000 3

Please let me know sort card to get the output as mentioned above.
Thanks.
seethap
 
Posts: 4
Joined: Tue Sep 30, 2014 9:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Is it possible to have GROUP BY Functionality in SORT

 

Re: Is it possible to have GROUP BY Functionality in SORT

Postby BillyBoyo » Wed Jul 27, 2016 10:33 pm

Look at OUTFIL reporting features, SECTIONS with TRAILER3 with SUM, TOT/TOTAL, and REMOVECC, NODETAIL.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 262 times

Re: Is it possible to have GROUP BY Functionality in SORT

Postby NicC » Thu Jul 28, 2016 7:31 pm

You ask a sort question but post in the JCL section. JCL is not sort - neither DFSort nor SyncSort bith of which have their own section in the forum.

Moved to DFsort - if you have syncsort let us know.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2630
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 95 times

Re: Is it possible to have GROUP BY Functionality in SORT

Postby seethap » Thu Jul 28, 2016 11:47 pm

Thanks billybobo for the pointer provided.

Please accept my Apologies for posting this in JCL section.

The result yielded with the following sort card.

SORT FIELDS=COPY
OUTFIL FNAME=SORTOUT,REMOVECC,
NODETAIL,
SECTION=(1,10,
TRAILER3=(1,10,' TOTAL AMOUNT ',
TOT=(11,5,ZD,M5,LENGTH=10),
' NO. OF COUNT ',
COUNT=(M10,LENGTH=10)))

Please explain about the significance of using REMOVECC, TO=ZD instead of M5.
Thanks all !!
seethap
 
Posts: 4
Joined: Tue Sep 30, 2014 9:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Is it possible to have GROUP BY Functionality in SORT

Postby seethap » Thu Jul 28, 2016 11:49 pm

It is SECTIONS not SECTION.
seethap
 
Posts: 4
Joined: Tue Sep 30, 2014 9:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Is it possible to have GROUP BY Functionality in SORT

Postby Aki88 » Fri Jul 29, 2016 11:40 am

Hello,

Your solution is close the original requirement you'd shared in this post; though when you execute the code shared (after correcting SECTIONS), you'll notice that it returns a CC 16 for 'FNAME='; when same is cleaned up, the output generated should look something as below, which is very different from what you were looking for, notice the decimal truncation that has occurred due to usage of M5:


Output from the shared card:

SURESH 100 TOTAL AMOUNT      0.00  NO. OF COUNT          1
RAMESH 250 TOTAL AMOUNT      0.00  NO. OF COUNT          1
SURESH 150 TOTAL AMOUNT      0.00  NO. OF COUNT          1
SURESH 200 TOTAL AMOUNT      0.00  NO. OF COUNT          1
RAMESH 100 TOTAL AMOUNT      0.00  NO. OF COUNT          1

Expected output from original post:

RAMESH 35000 2
SURESH 45000 3
 


I have cleaned up your code a little bit; notice the differences:


//STEP002  EXEC PGM=SORT                                              
//SYSOUT   DD SYSOUT=*                                                
//SORTIN   DD *                                                      
SURESH 10000                                                          
RAMESH 25000                                                          
SURESH 15000                                                          
SURESH 20000                                                          
RAMESH 10000                                                          
/*                                                                    
//SORTOUT  DD SYSOUT=*                                                
//SYSIN    DD *                                                      
* SORT FIELDS=COPY REPLACED WITH VALID SORTING KEY TO GROUP DATA AND  
* FACILITATE SECTIONS PROCESSING.                                    
 SORT FIELDS=(1,6,CH,A)                                              
*                                                                    
* OUTFIL USED TO TAILOR THE OUTPUT WITH REPORTING FEATURES            
* REMOVECC USED TO REMOVE THE CARRIAGE CONTROL                        
* CHARACTER WHICH WILL BE INSERTED FOLLOWING USAGE OF                
* OUTFIL REPORTING FEATURE.                                          
* NODETAIL USED TO AVOID THE INPUT DATA CHUNK FROM                    
* GETTING COPIED TO SORTOUT DS.                                      
* SECTIONS BREAKS THE DATA INTO GROUPS DEFINED IN THE            
* SECTIONS KEY.                                                  
* TRAILER3 COMES BUNDLED WITH SECTIONS.                          
* TOT IS THE SUB-COMMAND OF TRAILER3, ALLOWS                    
* SUMMATION OF USER-DEFINED FIELDS.                              
* ZD IS ZONED DECIMAL FORMAT.                                    
* M10 IS THE EDIT MASK PATTERN, WHICH ALLOWS USER                
* TO CHOOSE HOW THEY WANT TO SEE THE OUTPUT DATA.                
* COUNT IS ANOTHER SUB-PARAMETER, WHICH DOES WHAT                
* THE NAME SAYS: IT COUNTS.                                      
 OUTFIL REMOVECC,NODETAIL,                                      
 SECTIONS=(1,6,                                                  
 TRAILER3=(1,6,                                                  
           C' TOTAL AMOUNT:',TOT=(8,5,ZD,M10,LENGTH=10),        
           C' COUNT:',COUNT=(M10,LENGTH=9)))                    
/*                                                              
 


Returns:


RAMESH TOTAL AMOUNT:     35000 COUNT:        2  
SURESH TOTAL AMOUNT:     45000 COUNT:        3  
 


The above code is more or less self-explanatory, for more details on edit mask patterns, data formats, refer DFSORT Application Programming Guide.

Hope this helps.
Aki88
 
Posts: 351
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 34 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post