Posted: Thu Aug 23, 2007 11:53 am Post subject: compare three files and write out matching records based on
I have three files FA, FB and FC. All are Fixed Block with LRECL = 260
Every record in FA is prefixed(position 1-3) by 500 and every record in FB by 600 and every record in FC by 700.
Also FA, FB, FC have one header and one trailer each.
The FA(500 records) have unique account numbers starting from 16 and with length 13.
For Every account in FA there will be one or more 600 records and MAY be one or more 700 records.
The rule is Every account in FA should have atleast one 600 record in FB and 700 record is optional .
For an account, No 600 or 700 record can exist without 500 record. 500 record is like a parent and 600 and 700 are child.
Also, No 700 record can exist without 500 record.
For the above requirement. I decided to follow the below three steps.
1)Check whether the accounts in FA with 500 record has atleast one 600 records.
IF not write the 500 record accounts to ERR file say Err-FA and the accounts that has matching 600 records to another file say New-FA.
2)Check Whether the accounts in FB with 600 records has 500 records(there may be child without parent). IF not write the 600 record accounts to ERR file say Err-FB and the accounts that has matching 500 record to another file say New-FB.
3)Check Whether the accounts in FC with 700 records has 500 records in the New-FA. IF not write the 700 record accounts to ERR file say Err-FC and the accounts that has matching accounts to another file say New-FC
If anyone knows better way to do it, Please let me know your views.
Joined: 26 Nov 2002 Posts: 12380 Topics: 75 Location: San Jose
Posted: Thu Aug 23, 2007 7:04 pm Post subject:
vak255,
There is an easier way to check existence in all the files.
1. concatenate all the 3 files together and then omit the header and trailer records and sort on the account number. Using IFTHEN put a sequence number to the records considering the file-indicator and account number as key.
ex: take the key 2142192760155 from your sample data.It is present in all the 3 files.(600 & 700 have dups)
3. Take the output from step 2 and sort on the account number and sum the file-indicator value.By doing so we will get the desired records
1. If the account number is present in all the 3 files then total values will be 1800(500+600+700)
2. If the account is only present in FA file then total is only 500.
3. If the account is only present in FB file then total is only 600.
4. If the account is only present in FC file then total is only 700.
5. If the account is present in both FA & FB then total is only 1100.
6. If the account is present in both FA & FC then total is only 1200.
7. If the account is present in both FB & FC then total is only 1300.
Joined: 26 Nov 2002 Posts: 12380 Topics: 75 Location: San Jose
Posted: Thu Aug 23, 2007 8:20 pm Post subject:
s_shivaraj,
hmm did you forget the fact that FB & FC files can have duplicate records? If there are duplicates then your totals would be off as you are totalling on only 1 byte.
ex: 1 500 record and 3 600 records and 1 700 record would yield a total of (1+6+5) = 12 and you are only considering 1 byte which would result in sum overflow.
Kolusu, Thats great. I really appreciate all the time you put in in helping out all of us.
I think I have not said it clearly what i am really looking for. Sorry for all the confusion, 500 and 600 and 700 records are not the total number of records, they are the record identification number you can see all records in FA starts with 500 and Fb - 600 and FC - 700.
I don't want to filter the duplicate accounts. All i want to filter is the parent(500) without 600(700 is optional) and childs(600,700) without parent.
The desired out put i am looking for is,
FA:
Code:
HH9001200708221027
5002007072929ST2142192760155 <- should goto New-FA
5002007072929ST2142195168586 <- should goto Err-FA (No 600-child though it has 700)
5002007072929ST2142198130614 <- should goto New-FA
5002007072929ST2142209977374 <- should goto Err-FA (No 600)
5002007072929ST2142210123035 <- should goto Err-FA (No 600)
TT9001200708221027
FB:
Code:
HH9001200708221027
6002007072929ST2142192760155 <- should goto New-FB
6002007072929ST2142192760155 <- should goto New-FB
6002007072929ST2142195161111 <- should goto err-FB (No parent -500)
6002007072929ST2142195161111 <- should goto err-FB
6002007072929ST2142195161111 <- should goto err-FB
6002007072929ST2142198130614 <- should goto New-FB
6002007072929ST2142198130614 <- should goto New-FB
6002007072929ST2142198130614 <- should goto New-FB
TT9001200708221027
FC: (should be compared with New-FA)
Code:
HH9001200708221027
7002007072929ST2142192760155 <- should goto New-FC
7002007072929ST2142192760155 <- should goto New-FC
7002007072929ST2142195168586 <- should goto Err-FC
7002007072929ST2142195168586 <- should goto Err-FC
7002007072929ST2142275754799 <- should goto Err-FC
TT9001200708221027
One final Note. all I need is the Merged file with valid records and merged/unmerged Err-file with invalid records. I don't need the headers and trailers in the output file so i can skip them as Kolusu said.
Joined: 26 Nov 2002 Posts: 12380 Topics: 75 Location: San Jose
Posted: Fri Aug 24, 2007 10:11 am Post subject:
vak255,
Quote:
I think I have not said it clearly what i am really looking for. Sorry for all the confusion, 500 and 600 and 700 records are not the total number of records, they are the record identification number you can see all records in FA starts with 500 and Fb - 600 and FC - 700.
I never assumed that they are the total number of records. I treated them as the Record identification numbers only.
Quote:
I don't want to filter the duplicate accounts. All i want to filter is the parent(500) without 600(700 is optional) and childs(600,700) without parent.
My job gives out the report listing just the account numbers. however if you need the detail then you will need another pass of the data.
If easytrieve is an option then you can do everything in just 1 pass
Thnaks for the quick response. I just want to make sure that what i said is clear.
My shop don't have easytrieve. So i have to do this using Sort, I don't mind going for another pass.
I will try yours and will update you.
Joined: 26 Nov 2002 Posts: 12380 Topics: 75 Location: San Jose
Posted: Fri Aug 24, 2007 3:42 pm Post subject:
Quote:
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
vak255,
Make sure that the Toolin statements have the continuation character hyphen (-). Also when you have an error then post both TOOLMSG & DFSMSG sysout messages.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum