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
 

 

Join & Concatenate

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

New User


Joined: 15 Apr 2008
Posts: 2
Location: chn

PostPosted: Thu Feb 02, 2012 4:49 am    Post subject: Join & Concatenate
Reply with quote

I have a requirement to Join two files and concatenate some fields from second file and append to the end of the first file.
* Highlighted the Join Key field in Green for two input files FILE1 & FILE2
* Highlighted the field in brown in FILE2 that has to be concatenated at the end of the FILE1
* Highlighted the New fields required in OUTPUT file in Orange.
FILE1 LRECL=100, KEY POSITION (11,4)
FILE2 LRECL=200,KEY POSITION (10,4)

File 1:
A|1000499|8453 |20863|734|||143|2 HEALTH AND BEAUTY AIDS|
A|1000499|5534 |20863|734|||143|2 HEALTH AND BEAUTY AIDS|
A|1000499|1086 |20863|734|||143|2 HEALTH AND BEAUTY AIDS|
A|1000499|1119 |20863|734|||143|2 HEALTH AND BEAUTY AIDS|


File 2:
Deal |8453 |0 |varchar(500) |AssignmentMethod |0 |ANA
Deal |5534 |0 |varchar(500) |AssignmentMethod |0 |ANA
Deal |1086 |0 |varchar(500) |AssignmentMethod |0 |ANA
Deal |1119 |0 |varchar(500) |AssignmentMethod |0 |ANA
Deal |8453 |0 |varchar(100) |AssignmentMethod |0 |Analytic
Deal |5534 |0 |varchar(100) |AssignmentMethod |0 |Analytic
Deal |1086 |0 |varchar(100) |AssignmentMethod |0 |Analytic
Deal |1119 |0 |varchar(100) |AssignmentMethod |0 |Analytic.
.
.
.
Deal |8453 |0 |int |AvgPurchaseFreq |0 |60
Deal |5534 |0 |int |AvgPurchaseFreq |0 |60
Deal |1086 |0 |int |AvgPurchaseFreq |0 |2
Deal |1119 |0 |int |AvgPurchaseFreq |0 |23


Output file

A|1000499|8453 |20863|734|||143|2 HEALTH AND BEAUTY AIDS |ANA|Analytic|60
A|1000499|5534 |20863|734|||143|2 HEALTH AND BEAUTY AIDS |ANA|Analytic|60
A|1000499|1086 |20863|734|||143|2 HEALTH AND BEAUTY AIDS |ANA|Analytic|2
A|1000499|1119 |20863|734|||143|2 HEALTH AND BEAUTY AIDS |ANA|Analytic|23
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: Thu Feb 02, 2012 5:33 am    Post subject:
Reply with quote

Your requirement isn't clear. Some questions:

Are there always three records with each key in input file2 as shown in your example? Are the three records always in the order shown or is there some other way of identifying which type of record it is? What is the position and length of the data you want from each of these three records?

To elaborate:

Each record in file2 with a particular key seems to have a different structure, e.g. 8453 record with ANA, 8453 record with Analytic, 8453 record with 60. How can we identify each type of record and where it's data is. For example, how can we identify the record with ANA and what is the position and length of ANA? How can we identify the record with Analytic and what is the position and length of Analytic? How can we identify the record 60 and what is the position and length of 60?

In the future, please just use code tags instead of using colors. With code tags, we can tell the positions of the fields. With colors, we can't.
Back to top
View user's profile Send private message
venkatesh.balaji1

New User


Joined: 15 Apr 2008
Posts: 2
Location: chn

PostPosted: Thu Feb 02, 2012 6:20 am    Post subject: Join & Concatenate
Reply with quote

Frank,

To elaborate my requirement . There may be as many records with each key in file2 ( Just showed you an example pattern of how the file will look).

File2 will be a VB file and I want to parse only the last field of the file2.
Suppose I have 7 fields in file2. I want to ignore first six fields and I am interested in the last field only and will append to FILE1. The Join key for FILE1 AND FILE2 will be (11,4) AND (10,4)
I cannot tell exactly where the last field will start. It may be 80,81 ,90 ,91 and so on. Data type of last field can be numeric or Alphanumeric and it will again be a Variable length field.

This file generated outside in frontend so I will not have any clues on the length of each record.This is the requirement I got and I am also very confused on how to handle this.

Thanks
Venkatesh
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: Thu Feb 02, 2012 6:50 am    Post subject:
Reply with quote

Sorry, but your description has too many "unknowns" for me to give you a solution.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7251

PostPosted: Thu Feb 02, 2012 1:02 pm    Post subject: Re: Join & Concatenate
Reply with quote

Code:
Deal    |8453     |0      |varchar(500)                       |AssignmentMethod                |0      |ANA


Can you please get rid of all the colours and use the Code tags to show the format of your files. If you don't know how, click on the Quote button here.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Thu Feb 02, 2012 10:59 pm    Post subject:
Reply with quote

venkatesh.balaji1,

Based on the following assumptions

1. Your file2 VB file data is '|' delimited and last field have a max field length of 30.
2. There are can be a max of 9 dups for each key in the VB file.(You can expand this upto 99 if you want by adding more IFTHEN statements and adjusting the field positions)

Code:

//STEP0100 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//INA      DD DSN=Your input VB 200 lrecl file,DISP=SHR
//INB      DD DSN=Your input FB 100 lrecl file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                         
//SYSIN    DD *                                                   
  OPTION COPY                                                     
  JOINKEYS F1=INA,FIELDS=(05,4,A)                                 
  JOINKEYS F2=INB,FIELDS=(11,4,A)                                 
  REFORMAT FIELDS=(F2:1,100,F1:9,30)                               

  INREC IFTHEN=(WHEN=INIT,                                         
  BUILD=(1,100,279X,101,30,C'|',SEQNUM,2,ZD,RESTART=(11,4))),     
  IFTHEN=(WHEN=GROUP,BEGIN=(411,2,ZD,EQ,1),PUSH=(101:380,31)),     
  IFTHEN=(WHEN=GROUP,BEGIN=(411,2,ZD,EQ,2),PUSH=(132:380,31)),     
  IFTHEN=(WHEN=GROUP,BEGIN=(411,2,ZD,EQ,3),PUSH=(163:380,31)),     
  IFTHEN=(WHEN=GROUP,BEGIN=(411,2,ZD,EQ,4),PUSH=(194:380,31)),     
  IFTHEN=(WHEN=GROUP,BEGIN=(411,2,ZD,EQ,5),PUSH=(225:380,31)),     
  IFTHEN=(WHEN=GROUP,BEGIN=(411,2,ZD,EQ,6),PUSH=(256:380,31)),     
  IFTHEN=(WHEN=GROUP,BEGIN=(411,2,ZD,EQ,7),PUSH=(287:380,31)),     
  IFTHEN=(WHEN=GROUP,BEGIN=(411,2,ZD,EQ,8),PUSH=(318:380,31)),     
  IFTHEN=(WHEN=GROUP,BEGIN=(411,2,ZD,EQ,9),PUSH=(349:380,31)),     
  IFTHEN=(WHEN=(411,2,ZD,EQ,1),OVERLAY=(132:248X)),               
  IFTHEN=(WHEN=(411,2,ZD,EQ,2),OVERLAY=(163:217X)),               
  IFTHEN=(WHEN=(411,2,ZD,EQ,3),OVERLAY=(194:186X)),               
  IFTHEN=(WHEN=(411,2,ZD,EQ,4),OVERLAY=(225:155X)),               
  IFTHEN=(WHEN=(411,2,ZD,EQ,5),OVERLAY=(256:124X)),               
  IFTHEN=(WHEN=(411,2,ZD,EQ,6),OVERLAY=(287:093X)),               
  IFTHEN=(WHEN=(411,2,ZD,EQ,7),OVERLAY=(318:062X)),               
  IFTHEN=(WHEN=(411,2,ZD,EQ,8),OVERLAY=(349:031X))                 

  OUTFIL REMOVECC,NODETAIL,                                       
    BUILD=(379X),
    SECTIONS=(11,4,TRAILER3=(1,379))                                 
//*                                                               
//JNF1CNTL DD *                                                   
  INREC PARSE=(%=(ENDBEFR=C'|'),                                   
               %01=(ENDBEFR=C'|',FIXLEN=4),                       
               %=(ENDBEFR=C'|'),                                   
               %=(ENDBEFR=C'|'),                                   
               %=(ENDBEFR=C'|'),                                   
               %=(ENDBEFR=C'|'),                                   
               %02=(ENDBEFR=C'|',FIXLEN=30)),                     
               BUILD=(1,4,%01,%02)                                 
//*
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Join key - Populate Zeros when Unpair... rexx77 DFSORT/ICETOOL 6 Thu May 12, 2016 12:22 am
No new posts Join Keys and DB2 Query jackare SYNCSORT 2 Sat Feb 13, 2016 5:57 am
No new posts Join Keys to compare two files senthamizh DFSORT/ICETOOL 2 Fri Feb 05, 2016 8:28 am


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