Finding out duplicate records

Posted by vignesh kumar on 29-Dec-2015 23:43

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?

All Replies

Posted by Mark Davies on 30-Dec-2015 00:35

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.

/*********************************************************************************************************/

Posted by vignesh kumar on 30-Dec-2015 01:12

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.

Posted by Mark Davies on 30-Dec-2015 01:33

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.

Posted by vignesh kumar on 30-Dec-2015 01:48

Hi,

The tables have only few fields are the same and others are different. Can you help on this?

Posted by Mark Davies on 30-Dec-2015 01:57

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.

Posted by vignesh kumar on 30-Dec-2015 04:14

Hi,

Am not able to go with Buffer compare even with exclude statement and please tell how to dynamically loop through the fileds.

Posted by Mark Davies on 30-Dec-2015 04:23

Would you be able to give me the structure (.df) of the two tables you are attempting to compare?

This thread is closed