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
 

 

Sort to concatenate or merge a column based on another field

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

New User


Joined: 07 Jul 2005
Posts: 41
Location: chennai

PostPosted: Fri Jul 26, 2013 3:00 pm    Post subject: Sort to concatenate or merge a column based on another field
Reply with quote

Hi,

I have a requirement to concatenate a column field values based on another field. below is the explanation

File:-
COL1 COL2
----- ------
0001 ABC
0001 DEF
0002 XYZ

After concatenating the out put should look like

COL1 COL2
----- ------
0001 ABC,DEF
0002 XYZ

not sure how to handle the requirement through sort or icetool,
please help.
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Fri Jul 26, 2013 3:10 pm    Post subject:
Reply with quote

You haven't really told us anything of use there. One could guess at the requirement being that the match is on the first field of the records, but that may be wrong.

So please think about what you want, and explain it clearly.

Also, please run the following and post the SYSOUT segment so that we know the sort product and release level.
Code:

//SORTCOPY EXEC PGM=SORT               
//SYSOUT   DD SYSOUT=*                 
//SORTIN   DD *                       
DUMMY                                 
/*                                     
//SORTOUT  DD SYSOUT=*                 
//SYSIN    DD *                       
  OPTION COPY                         
/*                                     
Back to top
View user's profile Send private message
balajiofcrrcoe

New User


Joined: 07 Jul 2005
Posts: 41
Location: chennai

PostPosted: Fri Jul 26, 2013 3:32 pm    Post subject:
Reply with quote

Hi Expat,

Thanks for the quick response

My requirement is that. we have a ledger list in column1 and list of office names in column2. one or more office's can have the same ledger number. we have got a requirement to create a report with ledger number in one column and all the office names under the ledger in the second column. Here maximum number of offices for a ledger in unknown, so i cannot anticipate the report size.

Here is the sysout of the job which you have asked me run:-
Code:

ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE751I 0 C5-K76982 C6-K90026 C7-K82419 C8-K67572 E9-K60824 C9-BASE   E5-K80744 E7-K79990
ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED
ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R12 - 05:50 ON FRI JUL 26, 2013 -
           SORT FIELDS=COPY
ICE201I H RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE751I 0 C5-K76982 C6-K90026 C7-K82419 C8-K67572 E9-K60824 C9-BASE   E5-K80744 E7-K79990
ICE142I 0 SYSOUS   NOT FOUND - SYSOUT USED
ICE193I 0 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 ENVIRONMENT SELECTED
ICE252I 1 PARMLIB OPTIONS WERE MERGED WITH INSTALLATION MODULE DEFAULTS
ICE088I 0 NXV3668A.STEP1   .        , INPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE093I 0 MAIN STORAGE = (MAX,10485760,10472542)
ICE156I 0 MAIN STORAGE ABOVE 16MB = (10350046,10350046)
ICE127I 0 OPTIONS: OVFLO=RC4 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC4 ,VLSCMP=N,SZERO=Y,RESET=Y,VSAMEMT=Y,DYNSPC=192
ICE128I 0 OPTIONS: SIZE=10485760,MAXLIM=2097152,MINLIM=450560,EQUALS=N,LIST=Y,ERET=ABEND,MSGDDN=SYSOUT
ICE129I 0 OPTIONS: VIO=Y,RESDNT=NONE,SMF=FULL ,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALOC=N             ,ABCODE=016
ICE130I 0 OPTIONS: RESALL=12288,RESINV=0,SVC=109 ,CHECK=Y,WRKREL=Y,OUTREL=Y,CKPT=N,COBEXIT=COB2
ICE131I 0 OPTIONS: TMAXLIM=10485760,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=Y,CFW=N,DSA=0
ICE132I 0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=N,TEXIT=N,LISTX=N,EFS=NONE    ,EXITCK=W,PARMDDN=DFSPARM ,FSZEST=N
ICE133I 0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=128 ,ODMAXBF=0,SOLRF=Y,VLLONG=N,VSAMIO=N,MOSIZE=MAX
ICE235I 0 OPTIONS: NULLOUT=RC0
ICE236I 0 OPTIONS: DYNAPCT=10 ,MOWRK=Y
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTOUT
ICE084I 0 BSAM ACCESS METHOD USED FOR SORTIN
ICE751I 1 EF-BASE   F0-K66717 E8-K79990
ICE090I 0 OUTPUT LRECL = 80, BLKSIZE = 80, TYPE = FB
ICE055I 0 INSERT 0, DELETE 0
ICE054I 0 RECORDS - IN: 1, OUT: 1
ICE052I 0 END OF DFSORT

EDIT: Code'd. Please use "Code" tags while posting code.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Jul 26, 2013 3:46 pm    Post subject:
Reply with quote

What is the Max of office names for a ledger number?
Back to top
View user's profile Send private message
balajiofcrrcoe

New User


Joined: 07 Jul 2005
Posts: 41
Location: chennai

PostPosted: Fri Jul 26, 2013 3:57 pm    Post subject:
Reply with quote

From current database, i have seen 1600 offices for a ledger.. it might increases if new offices are included to existing ledger.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Jul 26, 2013 4:00 pm    Post subject:
Reply with quote

Quote:
i have seen 1600 offices for a ledger.. it might increases if new offices are included to existing ledger.


I am afraid you need to write a program to write the sort card

To me creating a "Cobol" program sounds to be a better approach
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Jul 26, 2013 4:21 pm    Post subject: Reply to: Sort to concatenate or merge a column based on ano
Reply with quote

We need to know the lengths of your fields. So some examples please of the input with 0-5 offices per ledger. Do you wasnt a report or a file? (you'll need some very wide paper for a report).

You should also consider what you want the thing to look like if the number of offices per a single ledger can exceed what can be held on a maximum-length (Operating System) record.
Back to top
View user's profile Send private message
balajiofcrrcoe

New User


Joined: 07 Jul 2005
Posts: 41
Location: chennai

PostPosted: Fri Jul 26, 2013 5:09 pm    Post subject:
Reply with quote

Office name is 24 bytes in the database,The output will be an extract file and we will ftp to a server in csv format.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Fri Jul 26, 2013 6:03 pm    Post subject:
Reply with quote

Well, you need to answer one of the questions that you have not yet answered as 1600 * 24 = 38,400 + 1599 commas + length of ledger code + 1 more comma sortof exceeds max lrecl.
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 Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 24 Tue Dec 06, 2016 8:04 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm


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