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
 

 

How to insert a field from another file?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
sabarikanth

New User


Joined: 07 Jun 2010
Posts: 59
Location: coimbatore

PostPosted: Thu Feb 02, 2012 1:36 am    Post subject: How to insert a field from another file?
Reply with quote

Hi all,

I have a requirement as i need to compare two files based on a condition and I have to insert a field from second file to the first file,
Here is an example:

Input file 1:
Code:

S.No:1
Name: ABC
Age:20
Sex: male
Accnum: 12345
S.No:2
Name: DEF
Age:21
Sex: male
Accnum: 67890
S.No:3
Name: ZDE
Age:25
Sex: male
Accnum: 99945
Input file 2:
Code:

12345 QWE
67890 POI
56862 QWE
My output should be:
Code:

S.No:1
Name: ABC QWE
Age:20
Sex: male
Accnum: 12345
S.No:2
Name: DEF POI
Age:21
Sex: male
Accnum: 67890
S.No:3
Name: ZDE
Age:25
Sex: male
Accnum: 99945


here i have to match the Accnum field (in both the input files 1 and 2) and if matched insert it after Name field (in file 1). Can someone help on this.

Note: The position of Name field and Accnum field are constant.
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Thu Feb 02, 2012 2:16 am    Post subject:
Reply with quote

  1. May we assume that the "Accnum" record in the first data set is invariably three records (ETA) after the "Name" record?
  2. Are both data sets ordered by "Accnum"?
  3. Do you have a preferred means of accomplishing this? (*SORT, COBOL, PL/I, Rexx, LISP)
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 2:17 am    Post subject:
Reply with quote

Sab,

What is the RECFM and LRECL of input file1?

What is the RECFM and LRECL of input file2?

In input file 1, are there always 5 records (S.No:, Name:, Age:, Sex:, Accnum:) for each Accnum group?
Back to top
View user's profile Send private message
sabarikanth

New User


Joined: 07 Jun 2010
Posts: 59
Location: coimbatore

PostPosted: Thu Feb 02, 2012 2:43 am    Post subject: Reply to: How to insert a field from another file?
Reply with quote

Akatsukami:

Quote:
May we assume that the "Accnum" record in the first data set is invariably three records (ETA) after the "Name" record?
Accnum (char 5) can be at any place after Name field in the first file.

Quote:
Are both data sets ordered by "Accnum"?

File-2 is sorted based on Accnum.
Quote:

Do you have a preferred means of accomplishing this? (*SORT, COBOL, PL/I, Rexx, LISP)

Mostly Syncsort will do. But cobol is allowed.

Frank Yaeger:

RECFM and LRECL is FB and 80 respectively for both file-1 and file-2.

Quote:
In input file 1, are there always 5 records (S.No:, Name:, Age:, Sex:, Accnum:) for each Accnum group
?

No input file-1 may have more than 5 records((S.No:, Name:, Age:, Sex:, Accnum:) for each Accnum group.
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 3:00 am    Post subject:
Reply with quote

Quote:
Mostly Syncsort will do


Since you want Syncsort, not DFSORT, I'm out.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Thu Feb 02, 2012 3:03 am    Post subject:
Reply with quote

Frank Yaeger wrote:
Quote:
Mostly Syncsort will do


Since you want Syncsort, not DFSORT, I'm out.

Being neither a Syncsort maven nor a COBOL programmer, I must also recuse myself.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Thu Feb 02, 2012 3:46 am    Post subject: Reply to: How to insert a field from another file?
Reply with quote

So you have a file which contains fields instead of records? I bet someone thought that was a great idea.

You didn't answer the recently recused's question fully - is the first file, the groups of fields, in Accnum order?

Is there a limit to the number of fields making up these "distributed" records?

If so, in Cobol, it is just a modified two-file-match, storing the "distributed" record in a table, knowing where the Name is and adding it from the second file and flushing the table in the order it was stored.

If the first file is not in Accnum order, make it so.

In Syncsort, I don't know. I don't have documentation even.

If you can fully describe the file 1, how the combinations might appear, minimum/maximum no of fields per group. LRECL and RECFM.
Back to top
View user's profile Send private message
sabarikanth

New User


Joined: 07 Jun 2010
Posts: 59
Location: coimbatore

PostPosted: Thu Feb 02, 2012 4:21 am    Post subject: Reply to: How to insert a field from another file?
Reply with quote

Bill,
Input File-1:

File-1 has records in group. I'm not sure how many it may be. The group of records will start with S.no and end with ENDMSG. And also it is not defined as only S.no, Name, Age, Sex, Accnum will be in the group (this group may vary with records), As shown below

Code:

S.No:1
Name: ABC
Age:20
Sex: male
Accnum: 12345
ENDMSG
S.No:2
Name: DEF
Age:21
Sex: male
Addr: <Unknown>
Accnum: 67890
City: XX
State: YY
ENDMSG
S.No:3
Name: ZDE
Age:25
Sex: male
Accnum: 99945
Company: III
Code:87944555
ENDMSG


this file is sorted based on S.no. LRECL=80 RECFM=FB.

Input FILE-2:

File-2 has Accnum and Extname as shown below,

Code:
12345 QWE
67890 POI
56862 QWE


this file is sorted based on Accnum. Lrecl=80 and Recf=fb

My output:

i have to merge these two datasets with Accnum as basis. Once the Accnum matches I have to write the Extname from File-2 in Name field of File-1 as shown below,
Code:

S.No:1
Name: ABC QWE
Age:20
Sex: male
Accnum: 12345
ENDMSG
S.No:2
Name: DEF POI
Age:21
Sex: male
Addr: <Unknown>
Accnum: 67890
City: XX
State: YY
ENDMSG
S.No:3
Name: ZDE
Age:25
Sex: male
Accnum: 99945
Company: III
Code:87944555
ENDMSG


O/p file should be Recfm=Fb and Lrecl=80.

I hope i'm clear little bit.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Thu Feb 02, 2012 4:45 am    Post subject: Reply to: How to insert a field from another file?
Reply with quote

Personnaly I'd read file 1, making a new output file, 240-byte records, consisting of S.No, Name and Accnum.

Sort that file on Accnum.

Join to file 2 (with Sort) to get the name updated on the new file.

Sort on S.No.

Match to File 1 on S.No. Take next Name record and update it for your final output.

There may be a Sort-only solution using GROUP, but you'd have to investigate that yourself or hope someone who knows comes along.
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1703
Location: Australia

PostPosted: Fri Feb 03, 2012 3:28 pm    Post subject:
Reply with quote

Hi.

this may assist
Code:
//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD *                                                   
S.NO:1                                                             
NAME: ABC                                                         
AGE:20                                                             
SEX: MALE                                                         
ACCNUM: 12345                                                     
ENDMSG                                                             
S.NO:2                                                             
NAME: DEF                                                         
AGE:21                                                             
SEX: MALE                                                         
ADDR: <UNKNOWN>                                                   
ACCNUM: 67890                                                     
CITY: XX                                                           
STATE: YY                                                         
ENDMSG                                                           
S.NO:3                                                           
NAME: ZDE                                                         
AGE:25                                                           
SEX: MALE                                                         
ACCNUM: 99945                                                     
COMPANY: III                                                     
CODE:87944555                                                     
ENDMSG                                                           
//SORTOUT  DD DSN=&&T1,                                           
//            DISP=(,PASS,DELETE),                               
//            UNIT=SYSDA,                                         
//            SPACE=(TRK,(10,5),RLSE)                             
//SYSIN    DD *                                                   
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(50:SEQNUM,8,ZD,70:1,30)),     
        IFTHEN=(WHEN=GROUP,                                       
                BEGIN=(1,5,CH,EQ,C'S.NO:'),                       
                  END=(1,6,CH,EQ,C'ENDMSG'),                     
                 PUSH=(60:ID=8)),                                 
        IFTHEN=(WHEN=(1,7,CH,EQ,C'ACCNUM:'),                 
               OVERLAY=(70:X'0000000000000000'))             
  SORT FIELDS=(60,40,BI,A)                                   
 OUTREC IFTHEN=(WHEN=GROUP,                                   
                BEGIN=(70,8,CH,EQ,X'0000000000000000'),       
                 PUSH=(100:78,5))                             
/*                                                           
//STEP0200 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTJNF1 DD *                                               
12345 QWE                                                     
67890 POI                                                     
56862 QWE                                                     
/*                                                           
//SORTJNF2 DD DSN=&&T1,DISP=SHR                               
//SORTOUT  DD SYSOUT=*                                       
//SYSIN    DD *                                               
  JOINKEYS FILE=F1,FIELDS=(001,5,A)                           
  JOINKEYS FILE=F2,FIELDS=(100,5,A)                           
  JOIN UNPAIRED,F2                                   
  REFORMAT FIELDS=(F1:01,10,                         
                   F2:01,30,50,8)                   
  SORT FIELDS=(41,8,BI,A)                           
  OUTREC IFTHEN=(WHEN=(11,5,CH,EQ,C'NAME:'),         
                 BUILD=(11,10,7,3,80:X)),           
         IFTHEN=(WHEN=NONE,                         
                 BUILD=(11,30,80:X))                 
/*                                                   



Gerry
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Sat Apr 21, 2012 4:19 am    Post subject: Reply to: How to insert a field from another file?
Reply with quote

I finally connected current questions back to this topic, and reminded myself of the three weeks I spent (on-and-off, about four days) to get this into one JOINKEYS step, by which time it was a bit stale.

Started again from scratch this evening, for practice:

Code:
//STEP0100 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *
  JOINKEYS F1=INA,FIELDS=(91,5,A)
  JOINKEYS F2=INB,FIELDS=(1,5,A)
  JOIN UNPAIRED,F1
  REFORMAT FIELDS=(F1:1,100,?,F2:7,3)
  SORT FIELDS=(81,10,CH,A)
  OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(81,5),
                 PUSH=(91:1,80,102,3,96,5)),
         IFTHEN=(WHEN=(1,5,CH,EQ,C'NAME:'),
                 OVERLAY=(11:171,3))
  OUTFIL OMIT=(86,5,CH,EQ,C'00000'),
         IFOUTLEN=(80),
         IFTHEN=(WHEN=(86,5,CH,EQ,174,5,CH),
                BUILD=(1,80,/,
                       91,80))
//JNF1CNTL DD *
  INREC IFTHEN=(WHEN=GROUP,
                 BEGIN=(1,5,CH,EQ,C'S.NO:'),
                 PUSH=(81:ID=5,SEQ=5)),
        IFTHEN=(WHEN=(1,7,CH,EQ,C'ACCNUM:'),
                 OVERLAY=(91:9,5,86,5,ZD,SUB,+1,EDIT=(TTTTT),
                         86:C'00000'))
                                                             
//INA      DD *
S.NO:1
NAME: ABC
AGE:20
SEX: MALE
ACCNUM: 12345
ENDMSG
S.NO:2
NAME: DEF
AGE:21
SEX: MALE
ADDR: <UNKNOWN>
ACCNUM: 67890
CITY: XX
STATE: YY
ENDMSG
S.NO:3
NAME: ZDE
AGE:25
SEX: MALE
ACCNUM: 99945
COMPANY: III
CODE:87944555
ENDMSG
//INB      DD *
12345 QWE
67890 POI
56862 QWE


Gives:
Code:

S.NO:1         
NAME: ABC QWE 
AGE:20         
SEX: MALE     
ACCNUM: 12345 
ENDMSG         
S.NO:2         
NAME: DEF POI 
AGE:21         
SEX: MALE     
ADDR: <UNKNOWN>
ACCNUM: 67890 
CITY: XX       
STATE: YY     
ENDMSG         
S.NO:3         
NAME: ZDE     
AGE:25         
SEX: MALE     
ACCNUM: 99945 
COMPANY: III   
CODE:87944555 
ENDMSG   
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7232

PostPosted: Sat Apr 21, 2012 6:04 am    Post subject: Reply to: How to insert a field from another file?
Reply with quote

Ah, I forgot the other reason for not posting it.

Sab, don't get too excited. You are using Syncsort, so up to you to find out if you have the JNFnCNTL files.
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 17 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 File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 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
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm


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