combine multiple rows into one row using Syncsort



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

combine multiple rows into one row using Syncsort

Postby mberger » Tue May 11, 2010 1:05 am

dfsort-icetool-icegener/topic2846.html

Hello, I am using the syntax above (see linked topic) to do the same thing the original questioner was asking, but am receiving an error when I execute:

SYT000I SYNCTOOL RELEASE 1.6.0 - COPYRIGHT 2007 SYNCSORT INC.
SYT001I INITIAL PROCESSING MODE IS "STOP"
SYT002I "TOOLIN" INTERFACE BEING USED

SPLICE FROM(IN) TO(OUT) ON(321,8,ZD) KEEPNODUPS WITHANY -
WITH(81,80) WITH(161,80) WITH(241,80) USING(CTL1)
SYT050E INVALID OPERAND ON "SPLICE" STATEMENT
SYT030I OPERATION COMPLETED WITH RETURN CODE 12

SYT015I PROCESSING MODE CHANGED FROM "STOP" TO "SCAN" DUE TO OPERATION FAILURE

SYT004I SYNCTOOL PROCESSING COMPLETED WITH RETURN CODE 12
mberger
 
Posts: 9
Joined: Thu May 06, 2010 8:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: combine multiple rows into one row using sort

Postby Frank Yaeger » Tue May 11, 2010 2:34 am

The job works fine with DFSORT's ICETOOL. However, the SYT messages indicate you're using Syncsort's SYNCTOOL, not DFSORT's ICETOOL.

I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort.

This Forum is for DFSORT questions. Please ask Syncsort questions in the appropriate forum.
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: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: combine multiple rows into one row using Syncsort

Postby Alissa Margulies » Tue May 11, 2010 8:08 pm

mberger wrote:SYT000I SYNCTOOL RELEASE 1.6.0 - COPYRIGHT 2007 SYNCSORT INC.
.
.
.
SYT050E INVALID OPERAND ON "SPLICE" STATEMENT

Support for WHEN=GROUP was included in SyncSort for z/OS 1.3.2 (SYNCTOOL Release 1.6.2). You are running an older release of the product that does not include this support. If you are not able to upgrade, we will come up with an alternate solution for you.
Alissa Margulies
Syncsort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Alissa Margulies
Global moderator
 
Posts: 369
Joined: Tue Feb 26, 2008 11:15 pm
Location: USA
Has thanked: 1 time
Been thanked: 3 times

Re: combine multiple rows into one row using Syncsort

Postby Alissa Margulies » Tue May 11, 2010 11:20 pm

Here is a SyncSort for z/OS 1.3 job that will provide the requested output:
//STEP1 EXEC PGM=SORT   
//SYSOUT  DD SYSOUT=*   
//SORTOUT DD SYSOUT=*   
//SORTIN  DD *           
AAAAA                   
BBBBB                   
CCCCC                   
DD                       
EEEEE                   
FFFFF                   
GGGGG                   
HH                                 
//SYSIN   DD *                                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(321:SEQNUM,8,ZD)),             
  IFTHEN=(WHEN=INIT,OVERLAY=(329:321,8,ZD,MOD,+4,LENGTH=9)),       
  IFTHEN=(WHEN=(329,8,ZD,EQ,0),OVERLAY=(336:C'4'),HIT=NEXT),       
  IFTHEN=(WHEN=(329,8,ZD,EQ,1),OVERLAY=(321:SEQNUM,8,ZD),HIT=NEXT),
  IFTHEN=(WHEN=(329,8,ZD,NE,1),OVERLAY=(337:SEQNUM,8,ZD,           
          321:321,8,ZD,SUB,337,8,ZD,M11,LENGTH=8),HIT=NEXT),       
  IFTHEN=(WHEN=(329,8,ZD,EQ,1),BUILD=(1,80,240Z,321,8)),           
  IFTHEN=(WHEN=(329,8,ZD,EQ,2),BUILD=(80Z,1,80,160Z,321,8)),       
  IFTHEN=(WHEN=(329,8,ZD,EQ,3),BUILD=(160Z,1,80,80Z,321,8)),       
  IFTHEN=(WHEN=(329,8,ZD,EQ,4),BUILD=(240Z,1,80,321,8))           
  SORT FIELDS=(321,8,BI,A),EQUALS                                 
  SUM FIELDS=(1,8,BI,9,8,BI,17,8,BI,25,8,BI,33,8,BI,41,8,BI,       
             49,8,BI,57,8,BI,65,8,BI,73,8,BI,81,8,BI,89,8,BI,     
             97,8,BI,105,8,BI,113,8,BI,121,8,BI,129,8,BI,137,8,BI,
            145,8,BI,153,8,BI,161,8,BI,169,8,BI,177,8,BI,185,8,BI,
            193,8,BI,201,8,BI,209,8,BI,217,8,BI,225,8,BI,233,8,BI,
            241,8,BI,249,8,BI,257,8,BI,265,8,BI,273,8,BI,281,8,BI,
            289,8,BI,297,8,BI,305,8,BI,313,8,BI)                   
  OUTREC BUILD=(1,320)                                             
/*
Alissa Margulies
Syncsort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Alissa Margulies
Global moderator
 
Posts: 369
Joined: Tue Feb 26, 2008 11:15 pm
Location: USA
Has thanked: 1 time
Been thanked: 3 times

Re: combine multiple rows into one row using Syncsort

Postby mberger » Tue May 11, 2010 11:48 pm

I saw an example of SPLICE that does not use the GROUP keyword and I can execute but the results are not what I expected.

From input, I am taking 4 records and splicing them into one output record. Each input record is 80 bytes, the output is therefore 320 bytes. I would like the first record to occupy bytes 1-80 in the output file, second record to occupy bytes 81-160, third record to occupy bytes 161-240, and fourth record to occupy bytes 241-320.

When I execute the following, the first and third records appear in bytes 1-80 and 161-240 respectively, but the second and fourth records do not splice correctly and my output shows blanks for bytes 81-160 and 241-320.


//TOOLIN DD *
SPLICE FROM(IN) TO(OUT) ON(321,8,CH) WITH(81,80) -
WITH(161,80) WITH(241,80) USING(CTL1)
//CTL1CNTL DD *
INREC IFTHEN=(WHEN=INIT,
OVERLAY=(321:SEQNUM,8,ZD,329:321,8,ZD,MOD,+4,M11,LENGTH=1)),
IFTHEN=(WHEN=(329,1,ZD,EQ,2),
OVERLAY=(81:1,80,321:321,8,ZD,SUB,+1,M11,LENGTH=8)),
IFTHEN=(WHEN=(329,1,ZD,EQ,3),
OVERLAY=(161:1,80,321:321,8,ZD,SUB,+2,M11,LENGTH=8)),
IFTHEN=(WHEN=(329,1,ZD,EQ,4),
OVERLAY=(241:1,80,321:321,8,ZD,SUB,+3,M11,LENGTH=8))
OUTFIL BUILD=(1,80,81,80,161,80,241,80)
/*
//*
mberger
 
Posts: 9
Joined: Thu May 06, 2010 8:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: combine multiple rows into one row using Syncsort

Postby Alissa Margulies » Wed May 12, 2010 1:06 am

mberger,

Field (329,1) will never equal 4. This field will be 0 when there is no remainder. Try this modified JCL:

//TOOLIN DD *                                                 
  SPLICE FROM(IN) TO(OUT) ON(321,8,CH) WITHEACH -             
  WITH(81,80) WITH(161,80) WITH(241,80) USING(CTL1)           
//CTL1CNTL DD *                                               
  INREC IFTHEN=(WHEN=INIT,                                     
  OVERLAY=(321:SEQNUM,8,ZD,329:321,8,ZD,MOD,+4,M11,LENGTH=1)),
  IFTHEN=(WHEN=(329,1,ZD,EQ,2),                               
  BUILD=(81:1,80,321:321,8,ZD,SUB,+1,M11,LENGTH=8)),           
  IFTHEN=(WHEN=(329,1,ZD,EQ,3),                               
  BUILD=(161:1,80,321:321,8,ZD,SUB,+2,M11,LENGTH=8)),         
  IFTHEN=(WHEN=(329,1,ZD,EQ,0),                               
  BUILD=(241:1,80,321:321,8,ZD,SUB,+3,M11,LENGTH=8))           
  OUTFIL BUILD=(1,320)                                         
/*                           
Alissa Margulies
Syncsort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Alissa Margulies
Global moderator
 
Posts: 369
Joined: Tue Feb 26, 2008 11:15 pm
Location: USA
Has thanked: 1 time
Been thanked: 3 times

Re: combine multiple rows into one row using Syncsort

Postby mberger » Wed May 12, 2010 1:13 am

I was so close! Thanks so much for the help once again.

Mike
mberger
 
Posts: 9
Joined: Thu May 06, 2010 8:56 pm
Has thanked: 0 time
Been thanked: 0 time

Re: combine multiple rows into one row using Syncsort

Postby jenweiner » Fri Jun 11, 2010 9:46 pm

I am trying to find a way to take a file that contains one to multiple records with the same key value (SSN in this case) and string all the data from those multiple matching records together and write out as one record to the output file. In looking at my data, the highest number of records I have found thus far is over 300, but it will increase, so I'm looking for a way to handle an indefinite number of records. We are on version 1.2.1 of Syncsort on z/OS. Is this function possible with either our current version or one that is more current?

So, if my input data looks like this:

111111111morefields
111111111evenmorefields
222222222stuff
333333333yada
333333333yadayada
333333333yadayadayada

the output needs to be:

111111111morefields111111111evenmorefields
222222222stuff
333333333yada333333333yadayada333333333yadayadayada

Thanks for any help,
Jennifer
jenweiner
 
Posts: 4
Joined: Tue May 25, 2010 2:01 am
Has thanked: 0 time
Been thanked: 0 time

Re: combine multiple rows into one row using Syncsort

Postby dick scherrer » Sat Jun 12, 2010 12:00 am

Hello and welcome to the forum,

so I'm looking for a way to handle an indefinite number of records.
No version of Syncsort, cobol, assembler, etc can handle an indefinite number of records. . . .

There will have to be an allowance for if/when the data for one key exceeds the maximum that can be written into a single record. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: combine multiple rows into one row using Syncsort

Postby jenweiner » Sat Jun 12, 2010 12:38 am

Thanks for such a quick response, Dick. I'm very impressed with the quality of the responses I've seen on this forum - this is the first time I've posted, but definitely not the first time I've utilized prior answers.

I understand your point. I guess it would have been more correct for me to say that there isn't a "fixed number" of records that need to be combined rather than saying an "indefinite number". There's an example earlier in this thread where they took 4 records and combined them onto one output, but it looks like you have to code for each of those 4 records. That's fine for 4 records, but kind of impractical for 300+, especially when that number will be increasing. Oh, and I have 240 files that I have to do this same thing for.

For the record, I don't believe SYNCSORT is the right tool to be doing this work, but am under management direction until I can prove them wrong. I think I may look into writing a REXX to do this instead. If anyone has any better ideas of how to do this, I would appreciate any input.
jenweiner
 
Posts: 4
Joined: Tue May 25, 2010 2:01 am
Has thanked: 0 time
Been thanked: 0 time

Next

Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post