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
 

 

Concatenation of multiple rows into single row

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

New User


Joined: 12 Feb 2007
Posts: 11
Location: mumbai

PostPosted: Fri Aug 17, 2007 5:09 pm    Post subject: Concatenation of multiple rows into single row
Reply with quote

Hi

I need to combine multiple rows into single row.

i/p file

name comment Date
aa comment 1 08-14-2007
aa comment 2 08-10-2007
aa comment 3 08-17-2007
ba abc 05-01-2007
ba def 08-10-2007
ccc comment 1 01-14-2007
ccc comment 2 04-10-2007
ccc comment 3 05-17-2007
ccc comment 4 05-14-2007


I am expecting output as. Comment column should have values from all rows and date should be Max date of all rows.

aaa comment1 comment 2 comment 3 08-17-2007
bbb abc def 08-10-2007
ccc comment1 comment 2 comment 3 comment4 05-17-2007
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 17, 2007 8:29 pm    Post subject:
Reply with quote

Your post is very confusing. Please use code tags to show the spacing in your records and give the starting position, length and format of each field.

Your input has aa, ba and ccc, but your output has aaa, bbb and ccc. Where did aaa and bbb come from?

What is the maximum number of records with the same name you can have? What is the position of the date in your output records?

What is the RECFM and LRECL of your input file. What RECFM and LRECL do you want for the output file?
Back to top
View user's profile Send private message
shivani_jha

New User


Joined: 12 Feb 2007
Posts: 11
Location: mumbai

PostPosted: Mon Aug 20, 2007 10:51 am    Post subject: Reply to: Concatenation of multiple rows into single row
Reply with quote

Hi

Below is pattern of input record .

Code:

Key           comment            Lst-updt-dt
AAA           comm1              05-12-07
AAA           comm2              05-27-07
AAA           comm3              06-15-07
BBB           comm1              05-23-07
BBB           comm2              07-01-07


outout summary requiredd is as comment column should be combination of all the records and date field should be Max of all the records.

Code:

key       comment                lst-dt
AAA       comm1 comm2 comm3      06-15-07
BBB       comm1 comm2            07-01-07


Here Record format is FB
LRECL - 4299 for input
LRECL - 35000 for output.

Maximum number of reapting records is 10.

Please provide a solution to get output usinf sort JCL.
Back to top
View user's profile Send private message
shivani_jha

New User


Joined: 12 Feb 2007
Posts: 11
Location: mumbai

PostPosted: Mon Aug 20, 2007 11:01 am    Post subject:
Reply with quote

Below are the layout for input and output records.

Layout of input file is

05 Key Pic X(3).
05 comment Pic x(4096)
05 Lst-updt-dt Pic x(10)

Record format . . . : FB
Record length . . . : 4199

Layout of output file is .
05 Key Pic X(3).
05 comment Pic x(32000)
05 Lst-updt-dt Pic x(10)

Record format . . . : FB
Record length . . . : 33592.
Back to top
View user's profile Send private message
shankar.v

Active User


Joined: 25 Jun 2007
Posts: 196
Location: Bangalore

PostPosted: Mon Aug 20, 2007 5:58 pm    Post subject:
Reply with quote

shivani_jha
Quote:
Record format . . . : FB
Record length . . . : 33592.

For fixed block maximum record length is 32760.
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: Mon Aug 20, 2007 8:42 pm    Post subject:
Reply with quote

You say each comment field is 4096 characters and that you can have a maximum of 10 repeating records. That would give a maximum length for the combined comments of 40960 characters. That's much longer than the 32000 bytes you show for the output comments field as well as the maximum length for an FB record. Something doesn't add up here.
Back to top
View user's profile Send private message
shivani_jha

New User


Joined: 12 Feb 2007
Posts: 11
Location: mumbai

PostPosted: Tue Aug 21, 2007 10:06 am    Post subject:
Reply with quote

We can use the maximum bytes it can accomodate. Lets say output comment field is PIC X(32000). LRECL of output is 32500. The rest of the data can be truncted from comment field.
We can also set maximum ocuurence of repeating records to 8 or less.

Is there any way of concatenating multiple rows of CHAR field into one fields using SORT/JCL.
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: Tue Aug 21, 2007 9:12 pm    Post subject:
Reply with quote

I don't really understand how you can truncate the comment fields. So I'll just show you a simpler example with a max of 4 comments of 10 bytes each. Hopefully, you can extrapolate whatever you need from that. Here's the DFSORT/ICETOOL example:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//SORTDIAG DD DUMMY
//IN DD *
AAA           comm1              05-12-07
AAA           comm2              05-27-07
AAA           comm3              06-15-07
BBB           comm1              05-23-07
BBB           comm2              07-01-07
AAA           comm4              05-12-07
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD SYSOUT=*
//TOOLIN   DD    *
SELECT FROM(IN) TO(T1) ON(1,3,CH) FIRST USING(CTL1)
SORT FROM(IN) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,3,CH) WITHEACH -
  WITH(11,10) WITH(21,10) WITH(31,10) WITH(41,10)
/*
//CTL1CNTL DD *
  SORT FIELDS=(1,3,CH,A,40,2,CH,D,34,5,CH,D)
  OUTFIL FNAMES=T1,BUILD=(1,3,51:34,8,80:X)
/*
//CTL2CNTL DD *
  OPTION EQUALS
  SORT FIELDS=(1,3,CH,A)
  OUTREC IFOUTLEN=80,
    IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,2,ZD,RESTART=(1,3))),
    IFTHEN=(WHEN=(81,2,ZD,EQ,+1),BUILD=(1,3,11:15,10)),
    IFTHEN=(WHEN=(81,2,ZD,EQ,+2),BUILD=(1,3,21:15,10)),
    IFTHEN=(WHEN=(81,2,ZD,EQ,+3),BUILD=(1,3,31:15,10)),
    IFTHEN=(WHEN=(81,2,ZD,EQ,+4),BUILD=(1,3,41:15,10))
/*


OUT will have:

Code:

AAA       comm1     comm2     comm3     comm4     06-15-07 
BBB       comm1     comm2                         07-01-07 


If your records are already in order by positions 1-3 as shown in your original example, you can use a COPY as the second operator instead of a SORT and remove the SORT statement from CTL2CNTL.
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 Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am


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