combine multiple rows into one row using sort



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

combine multiple rows into one row using sort

Postby sharmiladevi » Wed Jan 06, 2010 6:28 pm

Hi All,

I have a requirement to combine multiple rows in input file into one record in output file.

Input file (FB,LRECL=80):

AAAAA
BBBBB
CCCCC
DD
EEEEE
FFFFF
GGGGG
HH

Requirement : combine 4 ROWS into 1 ROW in the output file

Output file(FB,LRECL=337):

AAAAABBBBBCCCCCDD
EEEEEFFFFFGGGGGHH

Thanks
Sharmila T
sharmiladevi
 
Posts: 6
Joined: Fri Mar 21, 2008 12:17 am
Has thanked: 0 time
Been thanked: 0 time

Re: combine multiple rows into one row using sort

 

Re: combine multiple rows into one row using sort

Postby Frank Yaeger » Wed Jan 06, 2010 10:32 pm

Your requirement is not clear. Do you want to remove the trailing blanks from each record before you combine them, or combine all 80 bytes from each record (with trailing blanks), or what? Why would the output have LRECL=337 instead of LRECL=320 (4 * 80)?

Would the output records really be:

   80          80         80     80
|AAAAA......|BBBBB....|CCCCC...|DD...|
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: combine multiple rows into one row using sort

Postby sharmiladevi » Thu Jan 07, 2010 1:01 am

Hi Frank,
Yes. I want to combine all 80 bytes from each record. Assume the output length would have LRECL=320. The output records in your example is correct.

Thanks,
Sharmila T
sharmiladevi
 
Posts: 6
Joined: Fri Mar 21, 2008 12:17 am
Has thanked: 0 time
Been thanked: 0 time

Re: combine multiple rows into one row using sort

Postby Frank Yaeger » Thu Jan 07, 2010 2:21 am

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

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DSN=...  input file (FB/80)
//OUT DD DSN=...  output file (FB/320)
//TOOLIN   DD    *
SPLICE FROM(IN) TO(OUT) ON(321,8,ZD) KEEPNODUPS WITHANY -
  WITH(81,80) WITH(161,80) WITH(241,80) USING(CTL1)
/*
//CTL1CNTL DD *
  OPTION COPY
  INREC IFOUTLEN=328,
    IFTHEN=(WHEN=GROUP,RECORDS=4,PUSH=(321:ID=8,329:SEQ=1)),
    IFTHEN=(WHEN=(329,1,ZD,EQ,2),BUILD=(81:1,80,321:321,8)),
    IFTHEN=(WHEN=(329,1,ZD,EQ,3),BUILD=(161:1,80,321:321,8)),
    IFTHEN=(WHEN=(329,1,ZD,EQ,4),BUILD=(241:1,80,321:321,8))
  OUTFIL FNAMES=OUT,BUILD=(1,320)
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: combine multiple rows into one row using sort

Postby sharmiladevi » Fri Jan 08, 2010 8:52 pm

Thanks a lot.. will try this code and let you know the results

Thanks
Sharmila T
sharmiladevi
 
Posts: 6
Joined: Fri Mar 21, 2008 12:17 am
Has thanked: 0 time
Been thanked: 0 time

Re: combine multiple rows into one row using sort

Postby upendra_water » Wed Jan 13, 2010 6:16 pm

Wow. Just one word Frank. Amazing. :o
I just tested this and it works perfectly.
upendra_water
 
Posts: 33
Joined: Wed Nov 25, 2009 10:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: combine multiple rows into one row using sort

Postby Vicky007 » Tue Sep 28, 2010 3:24 pm

hi all,

The requirement is basically similar; but i need to combine based on a specific criteria. PFB the I/P n required O/P.
Input file (FB,LRECL=80):

@AAAAA
BBBBB
CCCCC
DD
@CCC
AAAAA
@FFFF
@DDDDDD
PPPPP
VVVVV
GGGGG
HHHHHH

Required O/P
@AAAAABBBBBCCCCCDD
@CCCAAAAA
@FFFF
@DDDDDDPPPPPVVVVVGGGGGHHHHHH

I Should combine the records until I find '@' in the first byte of the record.
I want to remove the trailing blanks from each record before I combine them.

Please provide ur suggestion.

Thanks in advance!
Vicky SK
Vicky007
 
Posts: 7
Joined: Tue Sep 28, 2010 3:12 pm
Has thanked: 0 time
Been thanked: 0 time

Re: combine multiple rows into one row using sort

Postby Frank Yaeger » Wed Sep 29, 2010 2:22 am

In the private message you sent me, you added this to the requirement:

3) There is no limit for the max. number of records between 2 '@'.


Really - if there were 5000 * 80 byte records, how would you combine them into one 40000 byte record given that exceeds the maximum allowed LRECL?

For the technique I used previously and the extension of that technique to do the grouping by @, there are definitely practical limits to the number of records that can be joined and the resulting joined record length.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: combine multiple rows into one row using sort

Postby Vicky007 » Wed Sep 29, 2010 8:17 am

Hi Frank,

My mistake; when I said 'There is no limit' I tried to mean the number of records b/w 2 '@' may vary. It can be from 1 to 50. We may know it only during run time. Can you suggest something for this?

Thanks in advance!
Vicky SK
Vicky007
 
Posts: 7
Joined: Tue Sep 28, 2010 3:12 pm
Has thanked: 0 time
Been thanked: 0 time

Re: combine multiple rows into one row using sort

Postby Vicky007 » Wed Sep 29, 2010 8:24 pm

Hi Frank,
I used the code you suggested to Sharmiladevi and wrote the below code. It worked good and I got the desired output. However, when I tried for records more than 50 to be joined(ie, number of records b/w 2 '@'), I'm getting error. Because am using more than 50 'WITH' keyword in SPLICE operator.

Do you have any other suggestion to address this issue? Below is the piece of code that I used assuming I need to combine 50 records into one.

//TOOLIN DD *
SPLICE FROM(IN) TO(OUT) ON(30001,8,ZD) KEEPNODUPS WITHANY -
WITH(81,80) WITH(161,80) WITH(241,80) WITH(321,80) WITH(401,80) -
"
"
"
WITH(4001,80) USING(CTL1)
/*
//CTL1CNTL DD *
OPTION COPY
INREC IFOUTLEN=30008,
IFTHEN=(WHEN=GROUP,BEGIN=(1,1,CH,EQ,C'@'),
PUSH=(30001:ID=8,30009:SEQ=5)),
IFTHEN=(WHEN=(30009,5,ZD,EQ,2),BUILD=(81:1,80,30001:30001,8)),
IFTHEN=(WHEN=(30009,5,ZD,EQ,3),BUILD=(161:1,80,30001:30001,8)),
"
"
"
IFTHEN=(WHEN=(30009,5,ZD,EQ,51),BUILD=(4001:1,80,30001:30001,8))
OUTFIL FNAMES=OUT,BUILD=(1,30000)
/*

Please provide ur suggestion.

Thanks in advance!
Vicky SK
Vicky007
 
Posts: 7
Joined: Tue Sep 28, 2010 3:12 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post