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
 

 

Comparing flat files and updating based on matching criteria

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

New User


Joined: 27 Dec 2005
Posts: 58

PostPosted: Tue Nov 13, 2007 5:06 pm    Post subject: Comparing flat files and updating based on matching criteria
Reply with quote

We have the following requirement:

FILE1:(INPUT FILE-->FILE3 has 1 record for every record from FILE1).
FIELD1-----FIELD2---FIELD3---FIELD4
------------------------------------------
------------------------------------------

FILE2:(INPUT FILE-->Used for matching with FILE1)
FIELD1-----FIELD2---FIELD3---FIELD4
------------------------------------------
------------------------------------------

FILE3:(OUTPUT FILE)
FIELD1-----FIELD2---FIELD3---FIELD4---FIELD5(From FIELD4 of FILE2)
------------------------------------------
------------------------------------------


Both FILE1 and FILE2 are flat files.We need the output on a third flat file FILE3.

The criteria is the following:
1.The number of records in FILE3 is same as number of records in FILE1.
2.FILE2 is used for matching.
3.FILE3 has 5 fields.The 4 fields of FILE1 and an additional field which would be FIELD4 of FILE2(Populated by either SPACES or valid value)
3.IF FIELD1/FIELD2 and FIELD3 values of FILE1 and FILE2 match then
The value of FIELD4 of FILE2 needs to be appended to that FILE1 record
and the 5 fields need to be written to FILE3.
4.For those FILE1 records where the 3 fields do not match the corresponding values in FILE2 we need SPACES for the last field in FILE3.

Kindly let me know if there is a solution for this using SYNCSORT or we need to write a program for this. I think I can solve this programmatically except that it might take longer.
Back to top
View user's profile Send private message

Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1288
Location: Chennai, India

PostPosted: Tue Nov 13, 2007 5:39 pm    Post subject: Reply to: Comparing flat files and updating based on matchin
Reply with quote

morpheus,

You need to provide few more details on the field positions. Use this JCL for your requirement and change the same as per your field positions.

Code:
//STEP01   EXEC PGM=SYNCTOOL                                       
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//IN1      DD *                                                   
11223344                                                           
11223344                                                           
AABBCCDD                                                           
/*                                                                 
//IN2      DD *                                                   
11223388                                                           
11XX3388                                                           
AABBCCFF                                                           
/*                                                                 
//T1       DD DSN=&&X1,UNIT=SYSDA,SPACE=(CYL,(15,5),RLSE),         
//            DISP=(MOD,PASS)                                     
//OUT      DD DSN=HLQ.XX.Y7,DISP=(,CATLG,DELETE)               
//OU1      DD DSN=HLQ.XX.Y8,DISP=(,CATLG,DELETE)               
//SYSPRINT DD   SYSOUT=*                                           
//SYSOUT   DD   SYSOUT=*                                           
//TOOLIN   DD *                                                   
  COPY FROM(IN1) TO(T1) USING(CTL1)                               
  COPY FROM(IN2) TO(T1) USING(CTL2)                                 
  SPLICE FROM(T1) TO(OUT) ON(1,6,CH) WITH(82,1) WITH(9,2) -         
  USING(CTL3) WITHALL                                               
/*                                                                   
//CTL1CNTL DD *                                                     
  OUTREC FIELDS=(1,8,81:C'11')                                       
/*                                                                   
//CTL2CNTL DD *                                                     
  OUTREC FIELDS=(1,6,2X,7,2,81:C'22')                               
/*                                                                   
//CTL3CNTL DD *                                                     
  OUTFIL FNAMES=OUT,INCLUDE=(81,2,CH,EQ,C'12'),OUTREC=(1,10)         
  OUTFIL FNAMES=OU1,INCLUDE=(81,2,CH,EQ,C'11'),OUTREC=(1,8,2X)       
/*                                                                   



output:

HLQ.XX.Y7

Code:
****** ***************************** Top of Data ******************************
000001 AABBCCDDFF                                                             
000002 1122334488                                                             
****** **************************** Bottom of Data ****************************


HLQ.XX.Y8

Code:
****** ***************************** Top of Data ******************************
000001 11223344                                                               
****** **************************** Bottom of Data ****************************


You need to add a step to concatenate both the files.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Tue Nov 13, 2007 10:56 pm    Post subject:
Reply with quote

morpheus007
Show some sample i/p and o/p records for clear understanding of your requirement.

Quote:
3.IF FIELD1/FIELD2 and FIELD3 values of FILE1 and FILE2 match then

Do you mean to say that if either filed1 or field2 and field3 match then.....?
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1288
Location: Chennai, India

PostPosted: Wed Nov 14, 2007 9:49 am    Post subject: Reply to: Comparing flat files and updating based on matchin
Reply with quote

Krisprems,

Quote:
Do you mean to say that if either filed1 or field2 and field3 match then.....?


morpheus wrote:

Quote:
4.For those FILE1 records where the 3 fields do not match the corresponding values in FILE2 we need SPACES
for the last field in FILE3.


hence i guess his requirement is to check for all the 3 fields. Anyways let us wait for the clarification from the OP.

This is one more example where people post their queries but never bother to reply back icon_sad.gif
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 Match two files using multiple keys a... santoshks1987 SYNCSORT 10 Fri Aug 18, 2017 10:50 am
No new posts Multiple VSAM files single output fil... Mohan Kothakota DFSORT/ICETOOL 11 Wed Aug 09, 2017 7:57 pm
No new posts How do you create Eclipse based dialogs jasorn IBM Tools 0 Thu Aug 03, 2017 5:05 pm
No new posts Split file based on comparsion to 2 f... Div Grad DFSORT/ICETOOL 3 Thu Jul 27, 2017 9:16 pm
No new posts getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm


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