apend last record in input file with the sum of two fields



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

apend last record in input file with the sum of two fields

Postby arya_starc » Wed Sep 28, 2016 10:39 am

Hi All,

My requirement is that I need to append one record in the output file in which the first five characters is "total" word and in other fields the sum of two fields from the input file.

EG:-
THIS IS MY INPUT FILE

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
23/03/2016  10  34   /*FILE 1*/
24/04/2016  20  56   /*FILE 2*/
26/08/2016  30  85   /*FILE 3*/
.
.
.
.
.
 


AND I NEED OUTPUT FILE LIKE THIS

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
23/03/2016  10  34   /*FILE 1*/
24/04/2016  20  56   /*FILE 2*/
26/08/2016  30  85   /*FILE 3*/
.
.
.
.
.
TOTAL        60   175
 
arya_starc
 
Posts: 60
Joined: Mon Sep 21, 2015 1:39 pm
Has thanked: 3 times
Been thanked: 0 time

Re: apend last record in input file with the sum of two fields

 

Re: apend last record in input file with the sum of two fiel

Postby enrico-sorichetti » Wed Sep 28, 2016 11:52 am

have You searched the forum ?
the issue has been discussed gazillions of times.
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2519
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 120 times

Re: apend last record in input file with the sum of two fiel

Postby Aki88 » Wed Sep 28, 2016 12:11 pm

Hello,

Look for OUTFIL --> TRAILER1 --> TOT
Aki88
 
Posts: 286
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 31 times
Been thanked: 23 times

Re: apend last record in input file with the sum of two fiel

Postby arya_starc » Wed Sep 28, 2016 3:36 pm

enrico-sorichetti wrote:have You searched the forum ?
the issue has been discussed gazillions of times.

hi enrico

I searched in forum..but not getting the correct post
dfsort-icetool-icegener/sum-fields-t120.html#p471
this I see but still not able to get.
arya_starc
 
Posts: 60
Joined: Mon Sep 21, 2015 1:39 pm
Has thanked: 3 times
Been thanked: 0 time

Re: apend last record in input file with the sum of two fiel

Postby arya_starc » Wed Sep 28, 2016 3:52 pm

Aki88 wrote:Hello,

Look for OUTFIL --> TRAILER1 --> TOT


while using trailer and outfil I an getting max cc 16
but I used with outrec and overlay

 SORT FIELDS=(1,2,ZD,A)                                          
 SUM FIELDS=(11,9,ZD,20,15,ZD)                                    
 OUTREC OVERLAY=(1:C'TOTAL',5X,                                  
                 (11,9,ZD),                                      
                 EDIT=(I,III,III),                                
                 (20,16,ZD),                                      
                 EDIT=(I,II,III,III,III))                        
*                                                                

 


but getting output as single record only
arya_starc
 
Posts: 60
Joined: Mon Sep 21, 2015 1:39 pm
Has thanked: 3 times
Been thanked: 0 time

Re: apend last record in input file with the sum of two fiel

Postby NicC » Wed Sep 28, 2016 3:57 pm

I do not see OUTFIL or TRAILER in that last post. Did you consult the manual or just copy an example and changed it with your own syntax?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2531
Joined: Sun Jul 04, 2010 12:13 am
Location: Down on the pig farm
Has thanked: 4 times
Been thanked: 88 times

Re: apend last record in input file with the sum of two fiel

Postby Aki88 » Wed Sep 28, 2016 4:13 pm

Hello,

arya_starc wrote:...but getting output as single record only


'SUM FIELDS' does precisely that (and more) - it sums the fields and produces unique records basis the SORT key specified in 'SORT FIELDS' statement.

A quote from the DFSORT programming guide; have highlighted the relevant portion:

The SUM control statement specifies that, whenever two records are found with
equal sort or merge control fields, the contents of their summary fields are to be
added, the sum is to be placed in one of the records, and the other record is to be
deleted
.


Since you have not shown what you had tried for OUTFIL/TRAILER1, it is difficult to recommend a correction for the RC- 16.

A hint:
a. Code a SORT FIELDS=COPY --> since you do not want records to be sorted, instead you just want a new summation record added
b. Use a simple OUTFIL REMOVECC, BUILD --> OUTFIL, to process output records prior getting written to outpit DS; REMOVECC, to remove carriage control character which will be inserted while using TRAILER1 reporting feature; BUILD to build the actual output
c. Couple this with TRAILER1 operator of OUTFIL, and TOTAL or TOT sub-operator of TRAILER1

Have a look at DFSORT Application Programming Guide for detailed description of these operators.
If you're looking for a quick code snippet, search the aforementioned operators, there are hundreds of working examples for this.
Aki88
 
Posts: 286
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 31 times
Been thanked: 23 times

Re: apend last record in input file with the sum of two fiel

Postby arya_starc » Wed Sep 28, 2016 6:26 pm

hi aki88,

I used below syntax

 SORT FIELDS=COPY                                              
  OUTFIL REMOVECC,BUILD=(1:1,35),                              
  TRAILER1(1:1C'TOTAL',                                        
          10:TOT=(11,9,ZD),                                    
          20:TOT(20,16,ZD))      
                             

but it reporting this error

                    20:TOT(20,16,ZD))                                          
                    $                                                          
ICE223A F REPORT FIELD ERROR                                                    
ICE751I 0 C5-I21470 C6-BASE   C7-K96411 C8-I25275 E7-I22412                    
 
arya_starc
 
Posts: 60
Joined: Mon Sep 21, 2015 1:39 pm
Has thanked: 3 times
Been thanked: 0 time

Re: apend last record in input file with the sum of two fiel

Postby arya_starc » Wed Sep 28, 2016 6:28 pm

NicC wrote:I do not see OUTFIL or TRAILER in that last post. Did you consult the manual or just copy an example and changed it with your own syntax?


000011 //STEP020  EXEC PGM=SORT,COND=(0,NE)                                    
000012 //SYSOUT   DD  SYSOUT=*                                                
000013 //SYSPRINT DD  SYSOUT=*                                                
000014 //SORTIN   DD  DSN=MVPS.AY.TESCO.CLUBPNT.S1.SE,DISP=SHR                
000015 //SORTOUT  DD  DSN=MVPS.AY.TESCO.CLUBPNT.SUMMARY.SE.OUT1,DISP=SHR      
000016 //SYSIN    DD  *                                                        
000017   SORT FIELDS=COPY                                                      
000018   OUTFIL REMOVECC,BUILD=(1:1,35),                                      
000019   TRAILER1(1:1C'TOTAL',                                                
000020           10:TOT=(11,9,ZD),                                            
000021           20:TOT(20,13,ZD))                                            
000022 //**********************************************************************


 


but getting max cc 16 for that
arya_starc
 
Posts: 60
Joined: Mon Sep 21, 2015 1:39 pm
Has thanked: 3 times
Been thanked: 0 time

Re: apend last record in input file with the sum of two fiel

Postby NicC » Wed Sep 28, 2016 6:32 pm

perhaps because 20:TOT( overlaps 10:TOT=
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2531
Joined: Sun Jul 04, 2010 12:13 am
Location: Down on the pig farm
Has thanked: 4 times
Been thanked: 88 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post