0

I am trying to get some duplicate records by using SOQL but i also want an analysis per day.

select max(id)maxID,count(id),name, ea_System__c, ea_BusinessLine_Code__c, ea_CustomerId__c,  count(Id)
from ea_SystemCodes__c 
group by name, ea_System__c, ea_BusinessLine_Code__c, ea_CustomerId__c
having count(Id) > 1

I want to also get the information of createdDate (not to group by), just to sort by, in order to understand when those records were created. Can anyone please help?

Improve this question
1

Seems to me you need to perform a second query to deal with that. This second query probably needs to be dynamic, using clauses that are generated from the "maxID" instance's values for ea_System__c, ae_BusinessLine_Code__c and ea_CustomerId__c, something like the following (I've made assumptions about datatypes as you didn't provide schema detail):

AggregateResult[] aggregateResults = ...; // Your query here
String query = 'SELECT Name, ea_System__c, ea_BusinessLine_Code__c, ea_CustomerId__c, CreatedDate FROM ea_SystemCodes__c WHERE ';
String orderBy = ' ORDER BY ea_System__c, ea_BusinessLine_Code__c, ea_CustomerId__c';
String[] conditions = new String[0];

for (AggregateResult result : aggregateResults) {
    String systemValue = String.escapeSingleQuotes((String) result.get('ea_System__c'));
    String bizCode = String.escapeSingleQuotes((String) result.get('ea_BusinessLine_Code__c'));
    String customerId = String.escapeSingleQuotes((String) result.get('ea_CustomerId__c'));

    // Need a sequence of WHERE condition clauses that select exact matches for the
    // found combinations of system, biz code and customer ID
    conditions.add('(ea_System__c = \'' + systemValue + '\' AND ' +
        'ea_BusinessLine_Code__c = \'' + bizCode + '\' AND ' +
        'ea_CustomerId__c = \'' + customerId + '\')');
}

query += String.join(conditions, ' OR ') + orderBy;

// Execute the generated query
ea_SystemCodes__c[] duplicateCodes = Database.query(query);

At this point you should have all the duplicates in groupings (including those returned as "maxID" values) because of the group by clause.

It is worth noting that if there are too many unique duplicate combinations of these three parameters the query generation could exceed the maximum length of an SOQL query statement (which is 100K).

Improve this answer
5
  • Hi Phil W and thank you for your answer. I try to execute the code you provided to me but i get an error on line 10 Method does not exist or incorrect signature: void escapeSingleQuotes(Object) from the type String. Do you maybe know what the problem is? – Giorgos Galaios Aug 24 '20 at 15:55
  • Oh, I didn't cast the parameters - AggregateResult.get returns Object... – Phil W Aug 24 '20 at 15:58
  • I have updated the example code. Note that this was written "in the browser" without any testing since I don't have your object model. – Phil W Aug 24 '20 at 15:59
  • Thank you so much for providing this help! One last question, since the developer console does not give the ability to view all the results, could you please suggest me a way that i could report on my results or somehow extract them ? – Giorgos Galaios Aug 24 '20 at 16:03
  • If you use System.debug in the iteration of the duplicateCodes you will get an output per duplicate and, as long as you keep these lines reasonably short, you can then see the details in the debug logs in the "Logs" tab of the developer console. – Phil W Aug 24 '20 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.