I have a requirement like this.
I am having 2 files FileA and FileB. I am comparing fileA with FileB.I have to create 2 files based on the comparison.If the File A is blank and file B has records all the records of File B has to be written to a insert dataset.If the FileA and FileB has records the condition should be
A)The records which are common to both fileA and fileB need to be eliminted
B)IF the key is only 12 in length starting from position one and filelength is 80,So whatever records which are differing which have the same key in fileA and fileB and which differ only from position 13-80 need to created in a seperate DSN as updated records
c) the records which differ are in fileB and which differ in key field with fileA should come as inserted records
d)the records which differ are in fileA and which differ in key field with fileB should come as Deleted records
eg
FileA
000000000001 AB
000000000002 A2
000000000003 A3
000000000004 A4
000000000005 A5
000000000011 A5
000000000012 A5
FileB
000000000001 AD
000000000002 A2
000000000003 B3
000000000004 A4
000000000009 A5
000000000006 A6
And the final o/p file should be
Updated file o/p1
000000000001 AD
000000000003 B3
o/p2(inserted records)
000000000006 A6
000000000009 A5
o/p3(deleted records)
000000000011 A5
000000000012 A5
000000000005 A5
The records should of the following format if both files have records
If the fileA is blank and fileb has records the o/p should be of folloing format
FileA
.................
.................
.................
The code is working fine for both inserted updated and deleted records if both files has records.But if the file1 is blank and file2 had records lot of records getting truncated ie, If the file1 is blank and file2 has records all the records in file2 should come in inserted records rite? But instead of that lot of records are getting lost in the inserted record file
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Your job doesn't match the input record you originally showed. You seem to be using 1-3 as the key in your job, but in your first post you showed input records like this:
000000000001 AB
Please show input and output records that match your job.
FileA
001 AB
002 A2
003 A3
004 A4
005 A5
011 A5
012 A5
FileB
001 AD
002 A2
003 B3
004 A4
009 A5
006 A6
And the final o/p file should be
Updated file o/p1
001 AD
003 B3
o/p2(inserted records)
006 A6
009 A5
o/p3(deleted records)
011 A5
012 A5
005 A5
The records should of the following format if both files have records
If the fileA is blank and fileb has records the o/p should be of folloing format
FileA
.................
.................
.................
The code is working fine when both the fileA and fileb has records .But if if the fileA is blank and Fileb has records lot of records are getting truncated.When i analysed the truncation is happeneing in step SELECT FROM(T1) TO(T2) ON(1,80,CH) NODUPS .But since the fileA is blank and Fileb has records truncation shouldn't happen at that step.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
If you had blank records in fileA, the SELECT with NODUPS would remove them. NODUPS says to keep records that don't have duplicates. Since all of the blank records would be duplicates, they would not be selected.
(I don't know what you mean by "truncated" - it normally means that bytes at the end of a record are dropped. But you seem to be using it to mean something else.)
I mean the records in fileb is getting dropped.ie, If fileA is blank and fileb has 1100 after the statement SELECT FROM(T1) TO(T2) ON(1,80,CH) NODUPS more than 300 records is getting dropped. As per the scenario since the fileA is blank all the records in fileb should come in final output rite? could you please suggest me how we can avoid the dropping of recordsinorder to get all reords in my output file.ie, if FileA is blank what changes can i make in the above code to get all records in Fileb to out put file
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
I really don't understand your scenario with regard to blank records in FileA.
When I run with blank records in FileA, I get all of the FileB records in the INSERT file and no records in the UPDATE and DELETE files. With blank records in FileA, none of the records in FileB have a match in FileA, so why wouldn't they be considered as inserted? Why would you expect blank records in the UPDATE and DELETE files?
I suppose if that's what you really want, you could specifically tag blank records with a sequence number so NODUPS won't eliminate them. Then you could specifically INCLUDE them in the UPDATE and DELETE files.
