Hi All,
Could you please help me in the below issue.
I need to compare two tables and find out the duplicates.
For eg: Customer A and customer B are two tables, table A and B has same fields and values but table A has 2 records one record with same fields& values as table B and other record only with difference in field document number.
So i need to compare the two tables and export the record which has different document number. After exporting i need to delete the record also.
Could you please give me a query for this?
Hi,
Hoping that I understood your question - check if the following makes sense:
I created two temp-tables (ttTable & ttTableB) where I created two records for Customer 1 but one with a different DocumentNumber. I then created a record in ttTableB that is the same as the first record in ttTableA.
Using the BUFFER-COMPARE statement I compared the fields in each of the tables while iterating through ttTableA and if there is a different in the DocumentNumber I export the data to your work folder in a file called TableAExport.d and then delete the record in ttTableA.
Finally I loop through table ttTableA and ttTableB to show the remaining records.
/*********************************************************************************************************/
DEFINE VARIABLE cDifferences AS CHARACTER NO-UNDO.
DEFINE TEMP-TABLE ttTableA NO-UNDO
FIELD CustomerNumber AS INTEGER
FIELD CustomerName AS CHARACTER
FIELD DocumentNumber AS CHARACTER.
DEFINE TEMP-TABLE ttTableB NO-UNDO
FIELD CustomerNumber AS INTEGER
FIELD CustomerName AS CHARACTER
FIELD DocumentNumber AS CHARACTER.
CREATE ttTableA.
ASSIGN
ttTableA.CustomerNumber = 1
ttTableA.CustomerName = "Customer 1"
ttTableA.DocumentNumber = "DocumentNo".
CREATE ttTableA.
ASSIGN
ttTableA.CustomerNumber = 1
ttTableA.CustomerName = "Customer 1"
ttTableA.DocumentNumber = "DocumentOther".
CREATE ttTableB.
ASSIGN
ttTableB.CustomerNumber = 1
ttTableB.CustomerName = "Customer 1"
ttTableB.DocumentNumber = "DocumentNo".
FOR EACH ttTableA:
FIND FIRST ttTableB
WHERE ttTableB.CustomerNumber = ttTableA.CustomerNumber
NO-ERROR.
IF AVAILABLE ttTableB THEN
DO:
BUFFER-COMPARE ttTableA TO ttTableB SAVE RESULT IN cDifferences.
IF cDifferences <> "" AND LOOKUP("DocumentNumber",cDifferences) > 0 THEN
DO:
OUTPUT TO VALUE(SESSION:TEMP-DIRECTORY + "TableAExport.d").
EXPORT ttTableA.
OUTPUT CLOSE.
DELETE ttTableA.
END.
END.
END.
FOR EACH ttTableA:
DISPLAY
ttTableA
WITH FRAME fTableA DOWN.
END.
FOR EACH ttTableB:
DISPLAY
ttTableB
WITH FRAME fTableB DOWN.
END.
/*********************************************************************************************************/
Hi Davies,
Thanks for your quick response!.
But is there any way without defining temptable and directly compare two database tables because here u have defined with 3 fields only and easy to compare but i have more number of fields around 100 in database table.
I just need to compare 2 tables and export the record which has different document number that is we call as duplicates.
Hi,
Are the two tables you are comparing exactly the same? Do they have the exact same structure (fields, etc)? If they do then you can use the BUFFER-COMPARE statement exactly like I did here.
If your tables only have a few fields that are the same then you will have to write some code to loop through the fields that do match and compare their values instead.
Hi,
The tables have only few fields are the same and others are different. Can you help on this?
If both your buffers contain fields that are named exactly the same then the BUFFER-COMPARE will compare those fields only - the non-matching ones will be ignored.
The example I gave would then still apply for the comparing bit and the cDifference variable will contain a comma-separated list of fields that did not match. If you are only interested in the one field and that should be the only difference then check that it is the only field in the list.
I am guessing that you are looping through one table and then finding matching records in the other (similar to my example). If that is the case, the BUFFER-COMPARE statement would be the same:
BUFFER-COMPARE <BufferA> TO <BufferB> SAVE RESULT IN cDifferences.
If you look at the help for BUFFER-COMPARE you will note that you are also able to exclude certain fields from the compare.
See if this might be the way you want to go - if not and you cannot use buffer-compare then let me know and I will show you how to dynamically loop through the fields in each of the buffers.
Hi,
Am not able to go with Buffer compare even with exclude statement and please tell how to dynamically loop through the fileds.
Would you be able to give me the structure (.df) of the two tables you are attempting to compare?