I/P File 1 : Data is
---------------------
123...........ABC
345...........PQR
456...........TUV
I/P File 2 : Data is
---------------------
123...........ABC
345...........XYZ
789...........MNO
Key is first 3 digits and assume the length of file is say some 40.
In the 2nd file the 2nd record is updated/having different data in last 3 positions(let me say it is from 38-40 positions(assume PIC clause as CHAR).
Basically the requirement is like the the records updated in file 2(345 record) should be written to O/P file and the new record(789 record) in file 2 should not be written.
O/P File : Data should as
----------------------------
345...........XYZ
Note: There will be millions of data so the KEY value should not be mentioned directly(I mean we should not be using "345" and can be used as (1,3,CH)...something like this).
The logic which you have given is working but I am facing a small problem and below is the same.
File 1:(Data which you have mentioned but adding another record to it)
-------
123ABC
234BCD
345CDE
456DEF
567EFG
678FGH
678RAM ===> I am adding this record
789GHI
File 2:(Data which you have mentioned but adding another record to it)
-------
234PQR
589BCD
678FGH
678RAM
678SAM ==> I am adding this record
989ABC
Now I am getting in the Output file as
234PQR
678RAM ==> This should not be according to my requirement
678SAM
Explanation: The file 1 record 678FGH compares with 678RAM and according to the logic given in sysin the record 678RAM comes to output file which actually should not come according to my requirement.
So,Is there a chance to handle such type of situation.
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
Rajendra Kumar,
Do you expect duplicate keys in both of your input files? If yes, please provide a sample input covering all possible scenarios and the expected output out of it. Mention all the rules for reaching your expected output. What would you expect in output if you can have the below input?
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Quote:
I would expect only the below record which is from 2nd file
This is not what was requested. . .
Quote:
please provide a sample input covering all possible scenarios and the expected output out of it.
Please provide the requested info showing the inputs with multiple sets of duplicates and the output you want from that input and explain why which output was the required output.
Here is my explanation and the output which I am expecting.
I am having 2 Input file(1 can be assumed as yesterdays file and the other one as todays file).let me say file1 as yesterday's file and file2 as today's file.
In file1 let me say there are 14 records(record number from 1...14) and in today's file(file2) let me say there are 18 records out of which 10 records are same as the records in file1 and 2 records(13 & 14 of file1) are deleted and 2 records(9 & 12) are updated there are newly 4 records in today's file(file2).
So now my requirement is that only the updated records(2 records) from yesterday's file(file1) are present in today's file(file2)...so only these 2 records should be present.Neither the delted records of file 1 or newly inserted records of file2 should be coming to output file.
Below is the explanation with the help of data
file1:
------
123...........ABC
345...........BCD
456...........CDE
678...........DEF
789...........EFG
987...........FGH
876...........GHI
876...........XYZ
876...........ABC
765...........HIJ
765...........BCD
765...........TUV
654...........IJK --> Will get deleted in file2
543...........JKL --> Will get deleted in file2
File 2:
-------
123...........ABC
345...........BCD
456...........CDE
678...........DEF
789...........EFG
987...........FGH
876...........GHI
876...........XYZ
876...........pqr --> UPDATED(value changed from "ABC" to "pqr")
765...........HIJ
765...........BCD
765...........wxy --> UPDATED(value changed from "TUV" to "wxy")
432...........PQR --> NEW
321...........TUV --> NEW
210...........MNO --> NEW
109...........NOP --> NEW
Output file:
-------------
876...........pqr --> UPDATED record
765...........wxy --> UPDATED record
As mentioned already the KEY is first 3 chars and the data that will get changed is the last 3 chars.....total file length can be taken as 40.
Please let me know if this is still confusing....Thx
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
A few more qns.
In your latest example, say for the key: 876, you have equal number of records(3 records) each in each input file. Is it possible to have unequal number of records for a key in both the files? If yes, show such an example and explain how you reached the output.
Is it possible to have unequal number of records for a key in both the files?............Ans:Yes
As I mentioned previously the files will be coming from DB2 table....for example in yesterday's file there are 3 records with KEY 876 and in today's file there's a chance that 1 record is deleted and othe other one is updated and also we can have a new record inserted in today's file.
Are your input files sorted on any field?...Answer:Yes,they are sorted on the complete record.
Anyway,Thx to all for your replies...I was able to do this using ICETOOL in 2 steps.
