09-23-2010 6:35 PM
Hi All,
I want to identify and display some duplicate data from the database.
With duplicates I mean rows in a table that contain identical information in a combination of fields (Last Name, First Name, City).
Example:
ID1 ID2 Last Name First Name City Phone
1005 2010 Krieger Jeff San Ramon 9252997100
1012 2010 Krieger Jeff San Ramon 9252997100
1017 2010 Krieger Jeff San Ramon 9252997100
Now I want to select the IDs (ID1 and ID2) that identify each duplicate rows.
Therefore I want to create a query with a subquery inside, similar to this:
SELECT ID1 ID2 LastName FirstName
FROM Customers
INTO TABLE
WHERE (LastName FirstName City) IN
(SELECT LastName FirstName City
FROM Customers
GROUP BY LastName FirstName City
HAVING COUNT(*) > 1)
ORDER BY LastName FirstName.
How would be the syntax in ABAP, because I am not able to define more than one field after WHERE?
Any Ideas how to identify the duplicate data and return the ids?
Thanks for any help!
09-23-2010 6:39 PM
I think you will have to write individual WHERE's for each condition and splice them together with ANDs
Rob
I originall ysaid OR but meant AND
Edited by: Rob Burbank on Sep 23, 2010 1:40 PM
09-23-2010 6:39 PM
I think you will have to write individual WHERE's for each condition and splice them together with ANDs
Rob
I originall ysaid OR but meant AND
Edited by: Rob Burbank on Sep 23, 2010 1:40 PM
09-23-2010 7:42 PM
Is this something you need to do via code or can you use an analysis tool? TAANA & TAANA_AV allow you to analyze distribution spreads by table fields (you can pick your table and the fields). It's primarily used for archiving analysis but it's helpfuul in other scenarios as well. It's not optimal for finding single record dupes but I thought I'd throw it out there.