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
 

 

Multiple Rows into Single row Multiple columns

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

New User


Joined: 22 Sep 2005
Posts: 15
Location: USA

PostPosted: Fri Apr 13, 2012 2:33 am    Post subject: Multiple Rows into Single row Multiple columns
Reply with quote

Hi, I am trying to figure out how to solve this particular requirement:

I will get a flat file as an input file. This input file will contain user information of some sort, separated by Pipes. For each user, we may have at max 10 rows in a file.
In the output file, we need to summarize all the information separated by pipes in a single row.

Input :
Code:
Sachin|USA|MF|Inquiry-only
Sachin|USA|MF|Update-Restricted
Sachin|USA|MF|Update-Global
Gary|USA|MF|Update-restricted
Gary|USA|MF|Inquiry-only
JOHN|USA|MF|Inquiry-only



Output file should contain:
Code:
Sachin|USA|MF|Inquiry-only |Update-Restricted|Update-Global||||||||
Gary|USA|MF|Update-restricted|Inquiry-only|||||||||
JOHN|USA|MF|Inquiry-only||||||||||


As you can see, one user can have more than one, but at max 10 access Levels. we need to summarize them against each user name,and some basic user information given in the input file.

Any Guesses ? I am trying to see if I can use JoinKeys or Splice for this,
however, will appreciate if I can get a helping hand real quick.

Thanks
Sachin
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 Apr 13, 2012 4:00 am    Post subject:
Reply with quote

What is the key here? Is it the first field (e.g. Sachin) or the first three fields (Sachin|USE|MF) or something else?

What is the maximum length of each field?

What is the RECFM and LRECL of the input file?

The way you show the output records indicates that you want them to contain 12 pipe signs (|) even if there aren't 10 records for a key. Is that really what you want?
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Apr 13, 2012 9:27 pm    Post subject:
Reply with quote

Assuming the input is FB and has the following layout

Code:

field-1 = 20 bytes
field-2 = 03 bytes
field-3 = 02 bytes
field-4 = 30 bytes


The following DFSORT/ICETOOL JCL will give you the desired results.

Code:

//STEP0100 EXEC PGM=ICETOOL                                           
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//IN       DD *                                                       
SACHIN|USA|MF|INQUIRY-ONLY                                             
SACHIN|USA|MF|UPDATE-RESTRICTED                                       
SACHIN|USA|MF|UPDATE-GLOBAL                                           
GARY|USA|MF|UPDATE-RESTRICTED                                         
GARY|USA|MF|INQUIRY-ONLY                                               
JOHN|USA|MF|INQUIRY-ONLY                                               
//OUT      DD SYSOUT=*                                                 
//TOOLIN   DD *                                                       
  SPLICE FROM(IN) TO(OUT) ON(1,28,CH) WITHANY KEEPNODUPS USING(CTL1) -
  WITH(060,30) WITH(091,30) WITH(122,30) WITH(153,30) WITH(184,30)   -
  WITH(215,30) WITH(246,30) WITH(277,30) WITH(308,30)                 
//*                                                                   
//CTL1CNTL DD *                                                       
  OPTION COPY                                                         
  INREC IFOUTLEN=338,IFTHEN=(WHEN=INIT,                               
  PARSE=(%01=(ENDBEFR=C'|',FIXLEN=20),%02=(ENDBEFR=C'|',FIXLEN=03),   
         %03=(ENDBEFR=C'|',FIXLEN=02),%04=(FIXLEN=30)),               
  BUILD=(%01,C'|',%02,C'|',%03,C'|',                                   
         30X,C'|',30X,C'|',30X,C'|',30X,C'|',30X,C'|',                 
         30X,C'|',30X,C'|',30X,C'|',30X,C'|',30X,C'|',                 
         %04)),                                                       
                                                                       
  IFTHEN=(WHEN=INIT,OVERLAY=(369:SEQNUM,2,ZD,RESTART=(1,28))),         
  IFTHEN=(WHEN=(369,2,ZD,EQ,01),OVERLAY=(029:339,30)),                 
  IFTHEN=(WHEN=(369,2,ZD,EQ,02),OVERLAY=(060:339,30)),                 
  IFTHEN=(WHEN=(369,2,ZD,EQ,03),OVERLAY=(091:339,30)),                 
  IFTHEN=(WHEN=(369,2,ZD,EQ,04),OVERLAY=(122:339,30)),                 
  IFTHEN=(WHEN=(369,2,ZD,EQ,05),OVERLAY=(153:339,30)),                 
  IFTHEN=(WHEN=(369,2,ZD,EQ,06),OVERLAY=(184:339,30)),                 
  IFTHEN=(WHEN=(369,2,ZD,EQ,07),OVERLAY=(215:339,30)),                 
  IFTHEN=(WHEN=(369,2,ZD,EQ,08),OVERLAY=(246:339,30)),                 
  IFTHEN=(WHEN=(369,2,ZD,EQ,09),OVERLAY=(277:339,30)),                 
  IFTHEN=(WHEN=(369,2,ZD,EQ,10),OVERLAY=(308:339,30))                 
                                                                       
  OUTFIL BUILD=(1,338,SQZ=(SHIFT=LEFT))                               
//*


The output from this job is
Code:

SACHIN|USA|MF|INQUIRY-ONLY|UPDATE-RESTRICTED|UPDATE-GLOBAL||||||||     
GARY|USA|MF|UPDATE-RESTRICTED|INQUIRY-ONLY|||||||||                   
JOHN|USA|MF|INQUIRY-ONLY||||||||||                                     
Back to top
View user's profile Send private message
ballalsachin

New User


Joined: 22 Sep 2005
Posts: 15
Location: USA

PostPosted: Mon Apr 16, 2012 10:22 pm    Post subject:
Reply with quote

Hi Frank and Kolusu, Thanks for responding.
Apologies for the delay on from myside in answering to Frank's questions.

The key was 'Sachin|USA|MF|', Record format is Fixed Block, while input Record length will be around 1000. Also, irrespective of whether the user has all 10 access levels or not, we have to show 10 pipe delimited columns (i really don't understand this part, but that's the requirement).

Thanks for helping me with this code. I will try out, and i am sure this will work.

As always, can't thank you enough for the great help.

Thanks
Sachin
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 Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
This topic is locked: you cannot edit posts or make replies. SDSF multiple spool datasets extracte... PJAlarcon CLIST & REXX 1 Fri Apr 21, 2017 10:50 pm
No new posts Validating file with multiple header/... mohitsaini DFSORT/ICETOOL 6 Thu Apr 13, 2017 1:53 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm


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