Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Parse & Sum in a VB file with Variable Length Key Fields

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sat Apr 20, 2013 2:20 pm    Post subject: Parse & Sum in a VB file with Variable Length Key Fields
Reply with quote

Hi,

I have an Input File(RECFM=VB & LRECL=1004), in which first three fields are key. 4th field is Amount.
My output file should have no duplicates and final sum of the amount for same set of keys.
If sum of the amount is zero, then we don't need that record in output file.
Output file should also be the same,i.e. RECFM=VB and LRECL=1004.

Input File:
Code:
XX|11111|001A|16.93|JOHNNY B. GOODE||INVALL||||
XX|2222|001A|-17.12|JOHNY DEPP II||INVALL||||
XX|33333|001|10.45|JOHNNY R. CASH||INVALL||||
XX|4444444|0002|15.93|JOHNNY ENGLISH||INVALL||||
XX|2222|001A|15.12|JOHN DEPP II||INVALL||||
XX|33333|001|-9.45|JOHNNY R. CASH||INVALL||||
XX|11111|001A|-16.93|JOHNNY B. GOODE||INVALL||||


Output File:

Code:
XX|2222|001A|-2.12|JOHNY DEPP II||INVALL||||
XX|33333|001|1.00|JOHNNY R. CASH||INVALL||||
XX|4444444|0002|15.93|JOHNNY ENGLISH||INVALL||||


I searched the forum and found this post somewhat similar, in terms of Parsing.

My current approach is to change the Source Program for this Input file and make all the fields Fixed Length Format and then use SORT to get the final output.

But then I was just wondering how It can be done using just SORT, without changing the Source Program.

Any thought/suggestion is much appreciated.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Sat Apr 20, 2013 2:47 pm    Post subject: Reply to: Prase & Sum in a VB file with Variable Length
Reply with quote

You could "un-CSV" them with PARSE in INREC.

SORT and SUM

Re-CSV them in OUTREC (SQZ, FINDREP).

OUTFIL OMIT for the zero summed value.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sat Apr 20, 2013 3:59 pm    Post subject:
Reply with quote

Thanks Bill for your quick response..

Some additional information:

File will have maximum 11 fields:
Code:
1|2|3|4|5|6|7|8|9|10|11


Maximum Field length would be:
Code:
1st - 02
2nd - 11
3rd - 10
4th - 12 (including Sign)
5th - 30
Rest all - 10


As per your suggestion, I have tried to create a Pseudo SORT Card as:

Code:

OPTION COPY
  PARSE=(%01=(ENDBEFR=C'|',FIXLEN=02),%02=(ENDBEFR=C'|',FIXLEN=11),   
         %03=(ENDBEFR=C'|',FIXLEN=10),%04=(ENDBEFR=C'|',FIXLEN=12),
         %05=(ENDBEFR=C'|',FIXLEN=30),%06=(ENDBEFR=C'|',FIXLEN=10),
         %07=(ENDBEFR=C'|',FIXLEN=10),%08=(ENDBEFR=C'|',FIXLEN=10),
         %09=(ENDBEFR=C'|',FIXLEN=10),%10=(ENDBEFR=C'|',FIXLEN=10),
         %11=(FIXLEN=10)),

  BUILD=(%01,C'|',%02,C'|',%03,C'|',%04,C'|',%05,C'|',
         %06,C'|',%07,C'|',%08,C'|',%09,C'|',%10,C'|',
         %11)),
 
  SORT FILEDS=(01,02,CH,A,
               04,11,CH,A,
               16,10,CH,A),
  SUM FIELDS=(27,12,ZD)

  OUTFIL INCLUDE=(27,12,ZD,NE,0)                                   
    BUILD=(1,4,5,1000,SQZ=(SHIFT=LEFT))



Is it similar to what you have suggested?
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sat Apr 20, 2013 9:07 pm    Post subject:
Reply with quote

Hi Bill,

Im trying with below SORT Card, but its not giving me the desired output:

Code:
   SORT FIELDS=COPY                                               
    INREC PARSE=(%00=(ABSPOS=5,ENDBEFR=C'|',FIXLEN=02),     
                 %01=(ENDBEFR=C'|',FIXLEN=11),               
                 %02=(ENDBEFR=C'|',FIXLEN=10),               
                 %03=(ENDBEFR=C'|',FIXLEN=12),               
                 %04=(ENDBEFR=C'|',FIXLEN=30),               
                 %05=(ENDBEFR=C'|',FIXLEN=10),               
                 %06=(ENDBEFR=C'|',FIXLEN=10),               
                 %07=(ENDBEFR=C'|',FIXLEN=10),               
                 %08=(ENDBEFR=C'|',FIXLEN=10),               
                 %09=(ENDBEFR=C'|',FIXLEN=10),               
                 %10=(FIXLEN=10)),                           
     BUILD=(1,4,5:%00,C'|',                                 
                8:%01,C'|',                                 
               20:%02,C'|',                                 
               31:%03,SFF,TO=ZD,LENGTH=12,C'|',             
               44:%04,C'|',                                 
               75:%05,C'|',                                 
               86:%06,C'|',                                 
               97:%07,C'|',                                 
              108:%08,C'|',                                 
              119:%09,C'|',                                         
              130:%10)                                               
     SORT FIELDS=(05,02,CH,A,                                       
                  08,11,CH,A,                                       
                  20,10,CH,A)                                       
     SUM FIELDS=(31,12,ZD)                                           
     OUTREC IFTHEN(WHEN=INIT,BUILD=(1,4,5,1000,SQZ(SHIFT=LEFT)))


But I'm getting below error:

Code:
                SORT FIELDS=(05,02,CH,A,               
 ICE002I 0 DUPLICATE OR CONFLICTING SORT     STATEMENT
                             08,11,CH,A,               
                             $                         
 ICE007A 0 SYNTAX ERROR                               
                             20,10,CH,A,               
                             $                         
 ICE007A 0 SYNTAX ERROR                               
                             31,12,ZD)                 
                             $                         
 ICE007A 0 SYNTAX ERROR                               
                SUM FIELDS=(31,12,ZD)                 
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Sat Apr 20, 2013 11:35 pm    Post subject: Reply to: Prase & Sum in a VB file with Variable Length
Reply with quote

You have SORT FIELDS=COPY which you'll need to get rid of, because you want to sort the data.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Sun Apr 21, 2013 8:32 pm    Post subject:
Reply with quote

Thanks Bill..for you continuous assistance..I got it working though I'm not sure if I have done it in most efficient manner.

SORT CARD:
Code:

//SYSIN    DD *
    INREC PARSE=(%00=(ABSPOS=5,ENDBEFR=C'|',FIXLEN=02),
                 %01=(ENDBEFR=C'|',FIXLEN=11),         
                 %02=(ENDBEFR=C'|',FIXLEN=10),         
                 %03=(ENDBEFR=C'|',FIXLEN=12),         
                 %04=(ENDBEFR=C'|',FIXLEN=30),         
                 %05=(ENDBEFR=C'|',FIXLEN=10),         
                 %06=(ENDBEFR=C'|',FIXLEN=10),         
                 %07=(ENDBEFR=C'|',FIXLEN=10),         
                 %08=(ENDBEFR=C'|',FIXLEN=10),         
                 %09=(ENDBEFR=C'|',FIXLEN=10),         
                 %10=(FIXLEN=10)),                     
     BUILD=(1,4,5:%00,C'|',                           
                8:%01,C'|',                           
               20:%02,C'|',                           
               31:%03,SFF,TO=ZD,LENGTH=12,C'|',       
               44:%04,C'|',                           
               75:%05,C'|',                           
               86:%06,C'|',                           
               97:%07,C'|',                           
              108:%08,C'|',                                           
              119:%09,C'|',                                           
              130:%10)                                               
     SORT FIELDS=(05,02,CH,A,                                         
                  08,11,CH,A,                                         
                  20,10,CH,A)                                         
     SUM FIELDS=(31,12,ZD)                                           
     OUTFIL INCLUDE=(31,12,ZD,NE,0),                                 
            IFTHEN(WHEN=INIT,                                         
               OVERLAY=(31:31,12,ZD,EDIT=(SIIIIIIIT.TT),SIGNS(+,-))),
            IFTHEN(WHEN=INIT,                                         
               BUILD=(1,4,5,1000,SQZ(SHIFT=LEFT,MID=C' '))),         
            IFTHEN=(WHEN=INIT,                                       
               FINDREP=(INOUT=(C' | ',C'|',C'| ',C'|',C' |',C'|')))   
/*                                                                   



Input:
Code:
XX|11111|001A|16.93|JOHNNY B. GOODE||INVALL|||| 
XX|2222|001A|-17.12|JOHNY DEPP II||INVALL||||   
XX|33333|001|10.45|JOHNNY R. CASH||INVALL||||   
XX|4444444|0002|15.93|JOHNNY ENGLISH||INVALL||||
XX|2222|001A|15.12|JOHN DEPP II||INVALL||||     
XX|33333|001|-9.45|JOHNNY R. CASH||INVALL||||   
XX|11111|001A|-16.93|JOHNNY B. GOODE||INVALL||||



Output:
Code:
XX|2222|001A|-2.00|JOHNY DEPP II||INVALL||||     
XX|33333|001|+1.00|JOHNNY R. CASH||INVALL||||     
XX|4444444|0002|+15.93|JOHNNY ENGLISH||INVALL||||
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Mon Apr 22, 2013 3:19 am    Post subject: Reply to: Parse & Sum in a VB file with Variable Length
Reply with quote

You only need the first four fields to be fixed in length, the three you SORT on and the field you SUM. You could make the %05 just to have FIXLEN=themaximumlengthfordata.

If you look at ENDAT instead of ENDBEFR you should be able to preserve the "|" without having to reset it for the first three fields.

You don't need all the column-numbers on the BUILD, as all of those positions is where DFSORT is going to put the data anyway. Makes for maintenance problem when a field-length changes if you code the columns.

You can put the "pipe" for the amount back with the OVERLAY where you are editing.

Your PARSEd field %05 will be "too long", it will have a load of trailing blanks. Look at VLTRIM on OUTFIL to get rid of those. You've probably got some trailing blanks on your records already?

You'll no longer need the FINDREP to get rid of blanks between the pipes.

Bear in mind, if you have fields with more than one embedded blanks, you're going to change those to one. If you want to preserve those, you'll have to use PAIR= on the SQZ.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Mon Apr 22, 2013 9:24 am    Post subject:
Reply with quote

Hi Bill,

I have made the changes for the first three points, but when I use only VLTRIM, in some of the fields I have leading spaces:

SORT Card:
Code:
//SYSIN     DD *                                                     
    INREC PARSE=(%00=(ABSPOS=5,ENDAT=C'|',FIXLEN=03),               
                 %01=(ENDAT=C'|',FIXLEN=12),                         
                 %02=(ENDAT=C'|',FIXLEN=11),                         
                 %03=(ENDAT=C'|',FIXLEN=12),                         
                 %04=(FIXLEN=90)),                                   
     BUILD=(1,4,5:%00,                                               
                8:%01,                                               
               20:%02,                                               
               31:%03,SFF,TO=ZD,LENGTH=12,C'|',                     
               44:%04)                                               
     SORT FIELDS=(05,02,CH,A,                                       
                  08,11,CH,A,                                       
                  20,10,CH,A)                                       
     SUM FIELDS=(31,12,ZD)                                           
     OUTFIL INCLUDE=(31,12,ZD,NE,0),VLTRIM=C' ',                     
            IFTHEN(WHEN=INIT,                                       
               OVERLAY=(31:31,12,ZD,EDIT=(SIIIIIIIT.TT),SIGNS(+,-))),
            IFTHEN(WHEN=INIT,                                       
               BUILD=(1,4,5,1000,SQZ(SHIFT=LEFT,MID=C' ')))         
/*


Output:
Code:

 ------------------------------------------------------------------------------
XX|2222| 001A| -2.00|JOHNY DEPP II||INVALL||||                                 
EE4FFFF44FFFC446F4FF4DDCDE4CCDD4CC44CDECDD4444                                 
77F2222F00011F002B00F1685804577099FF955133FFFF                                 
 ------------------------------------------------------------------------------
XX|33333| 001| +1.00|JOHNNY R. CASH||INVALL||||                                 
EE4FFFFF44FFF444F4FF4DDCDDE4D44CCEC44CDECDD4444                                 
77F33333F0001F0E1B00F16855809B03128FF955133FFFF                                 
 ------------------------------------------------------------------------------
XX|4444444| 0002| +15.93|JOHNNY ENGLISH||INVALL||||                             
EE4FFFFFFF44FFFF444FF4FF4DDCDDE4CDCDCEC44CDECDD4444                             
77F4444444F00002F0E15B93F16855805573928FF955133FFFF                             
 ------------------------------------------------------------------------------
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Mon Apr 22, 2013 11:01 am    Post subject: Reply to: Parse & Sum in a VB file with Variable Length
Reply with quote

You will still need a SQZ, as the first four fields can contain blanks after the PARSE.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 268
Location: Room: TREE(3). Hilbert's Hotel

PostPosted: Tue Apr 23, 2013 7:35 pm    Post subject:
Reply with quote

Thanks a lot Bill..Sorry for replying a little late..but I was busy..people at my site find this solution "something that can be used as a temporary fix" and suggested that I should write a Program in COBOL or PL/I to do it..and I'm working on that now..

Nevertheless..it was a good learning experience..thanks again :-)
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Tue Apr 23, 2013 8:39 pm    Post subject: Reply to: Parse & Sum in a VB file with Variable Length
Reply with quote

No problem.

Funny. You have to SORT the file anyway. The processing can be done at the same time without another pass of the data. I guess there is a client "paying" for the extra load (and now extra development) :-)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us