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
 

 

Vertical records into horizontal layout and sum by key

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

New User


Joined: 26 Aug 2005
Posts: 8

PostPosted: Fri Aug 19, 2011 9:53 pm    Post subject: Vertical records into horizontal layout and sum by key
Reply with quote

hi members,

I have a file having following data:

Code:

----+----1----+----2--
12345678900000000010CC
12345678900000000010AA
12345678900000000020BB
12345678900000000025CC
12345678900000000015AA
22222222220000000005AA
22222222220000000015CC
22222222220000000005CC


Is there any way to get output like this:

Code:

----+----1----+----2----+----3----+----4----+-
12345678900000000025AA0000000020BB0000000035CC
22222222220000000005AA0000000000BB0000000020CC


thanks in advance
Back to top
View user's profile Send private message

Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Fri Aug 19, 2011 9:56 pm    Post subject:
Reply with quote

whatis the maximum number of unique second key occurance is possible?(AA,BB,CC.. will it be always three?)
Back to top
View user's profile Send private message
carlmgu

New User


Joined: 26 Aug 2005
Posts: 8

PostPosted: Fri Aug 19, 2011 10:00 pm    Post subject:
Reply with quote

The second key always is three.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Fri Aug 19, 2011 10:07 pm    Post subject:
Reply with quote

carlmgu wrote:
The second key always is three.


RESIZE operator looks right candidate...
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Aug 19, 2011 10:10 pm    Post subject:
Reply with quote

Escapa wrote:
carlmgu wrote:
The second key always is three.


RESIZE operator looks right candidate...


Escapa,

Did you notice that OP's input is missing BB record for 222222222 key.

Carlmgru,

was that a typo ? and you always have AA , BB , CC records for each key at position 1-10 and your summed field is from 11-10 in ZD format?

Please Post the LRECL and RECFM and all the fields formats
Back to top
View user's profile Send private message
carlmgu

New User


Joined: 26 Aug 2005
Posts: 8

PostPosted: Fri Aug 19, 2011 10:27 pm    Post subject:
Reply with quote

input LRECL=22, RECFM=FB
1-10 primary key
11-20 amount
21-22 second key
output LRECL=46, RECFM=FB

not all primary keys have a second key

Code:

----+----1----+----2--
12345678900000000010CC
12345678900000000010AA
12345678900000000020BB
12345678900000000025CC
12345678900000000015AA
22222222220000000005AA
22222222220000000015CC
22222222220000000005CC
33333333330000000050CC
44444444440000000050AA
44444444440000000025BB
44444444440000000025BB


Code:

----+----1----+----2----+----3----+----4----+-
12345678900000000025AA0000000020BB0000000035CC
22222222220000000005AA0000000000BB0000000020CC
33333333330000000000AA0000000000BB0000000050CC
44444444440000000050AA0000000050BB0000000000CC
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Fri Aug 19, 2011 10:40 pm    Post subject:
Reply with quote

carlmgu,
Is your input file sorted on primary key already?

Thanks,
Back to top
View user's profile Send private message
carlmgu

New User


Joined: 26 Aug 2005
Posts: 8

PostPosted: Fri Aug 19, 2011 10:44 pm    Post subject:
Reply with quote

The input file is already sorted on primary key
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Aug 19, 2011 11:02 pm    Post subject:
Reply with quote

Here's a DFSORT job that will do what you asked for:

Code:

//S1 EXEC PGM=SORT                                             
//SYSOUT DD SYSOUT=*                                           
//SORTIN DD DSN=...  input file (FB/22)
//SORTOUT DD DSN=...  output file (FB/46)                       
//SYSIN DD *                                                   
  INREC IFOUTLEN=46, 
        IFTHEN=(WHEN=(11,10,CH,EQ,C' '),OVERLAY=(11:10C'0')),                                           
        IFTHEN=(WHEN=(21,2,CH,EQ,C'BB'),                       
          BUILD=(1,10,23:11,10)),                               
        IFTHEN=(WHEN=(21,2,CH,EQ,C'CC'),                       
           BUILD=(1,10,35:11,10))                               
 OPTION COPY                                                   
 OUTFIL REMOVECC,NODETAIL,                                     
   SECTIONS=(1,10,                                             
     TRAILER3=(1,10,TOT=(11,10,ZD,TO=ZD,LENGTH=10),C'AA',       
                    TOT=(23,10,ZD,TO=ZD,LENGTH=10),C'BB',       
                    TOT=(35,10,ZD,TO=ZD,LENGTH=10),C'CC'))     
Back to top
View user's profile Send private message
carlmgu

New User


Joined: 26 Aug 2005
Posts: 8

PostPosted: Sat Aug 20, 2011 12:04 am    Post subject:
Reply with quote

Thanks Frank!

I changed the code INREC because the job ended with 0C7

Code:

  INREC IFOUTLEN=46,                                         
      IFTHEN=(WHEN=(21,2,CH,EQ,C'AA'),                     
        BUILD=(1,10,11,10,C'AA',10C'0',C'BB',10C'0',C'CC')),
      IFTHEN=(WHEN=(21,2,CH,EQ,C'BB'),                     
        BUILD=(1,10,10C'0',C'AA',11,10,C'BB',10C'0',C'CC')),
      IFTHEN=(WHEN=(21,2,CH,EQ,C'CC'),                     
        BUILD=(1,10,10C'0',C'AA',10C'0',C'BB',11,10,C'CC'))


My problem is solved. Thanks All.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sat Aug 20, 2011 12:51 am    Post subject:
Reply with quote

In some cases blank fields for ZD values will cause a problem.

I added the following IFTHEN clause to my job above to take care of that:

Code:

        IFTHEN=(WHEN=(11,10,CH,EQ,C' '),OVERLAY=(11:10C'0')),


It's simpler than what you did, although that will work too.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sat Aug 20, 2011 1:28 am    Post subject:
Reply with quote

Alternatively, you could use UFF in TOT instead of ZD to handle the blanks directly:

Code:

//S2 EXEC PGM=SORT                                             
//SYSOUT DD SYSOUT=*                                           
//SORTIN DD DSN=...  input file (FB/22)
//SORTOUT DD DSN=...  output file (FB/46)                       
//SYSIN DD *                                                   
  INREC IFOUTLEN=46, 
        IFTHEN=(WHEN=(21,2,CH,EQ,C'BB'),                       
          BUILD=(1,10,23:11,10)),                               
        IFTHEN=(WHEN=(21,2,CH,EQ,C'CC'),                       
           BUILD=(1,10,35:11,10))                               
 OPTION COPY                                                   
 OUTFIL REMOVECC,NODETAIL,                                     
   SECTIONS=(1,10,                                             
     TRAILER3=(1,10,TOT=(11,10,UFF,TO=ZD,LENGTH=10),C'AA',       
                    TOT=(23,10,UFF,TO=ZD,LENGTH=10),C'BB',       
                    TOT=(35,10,UFF,TO=ZD,LENGTH=10),C'CC'))   
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts how to split records based on specifi... Venkata Ramayya DFSORT/ICETOOL 6 Wed Sep 28, 2016 3:20 am


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