function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
rsoese@systecsrsoese@systecs 

Limit on Salesforce.com Aggregate queries. Are those "hard" ones?

I just ran into two new (at least for me) limits of the Salesforce.com / Database.com platform that give me a shiver. I executed a SOQL query of the like:

SELECT field_x1, .., field_xN, SUM(field_y1),..., SUM(field_yM) FROM...
GROUP BY field_x1, .., field_xN

With N >40 and M > 50 I ran into:

MALFORMED_QUERY: maximum number of aliased fields exceeded: 100

and

MALFORMED_QUERY: Group By must contain 32 fields or less

Ok, ok! You probably do not often have a case where you need a SOQL statement with more than 68 aggregated and 32 grouped fields. One time is enough to break a software feature ;-)

Any experience how to prevent this? Submit a case, chunk query, ...?!
I haven't found a single word on this on Google.

AdrianCCAdrianCC

Hi,

 

Where exactly are you using this piece of code? A trigger, a controller, batch apex? 

 

Thanks,

Adi
rsoese@systecsrsoese@systecs

I am using this inside Batch Apex code using Iterable<AggregateResult>.
AdrianCCAdrianCC

Alright. Have you tried using Database.QueryLocator instead of the Iterable<sObject> ?

See here example: http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm

 

L.E.: since the problem is a soql limitation QueryLocator might not work as well.

I suggest you leave the aliases for the aggregate fields in place and remove the GROUP BY completly from the soql. I hope you have less than 100 aliases :).

At this moment you'll have the list with all the records.

You'll need to do some custom logic around it to implement the GROUP BY somehow for multiple fields... 

For what do you need so many GROUP BY?!!
rsoese@systecsrsoese@systecs

Hy Adrian,

 

I cannot use Database.QueryLocator as this does not work with aggregate queries.

 

I do not quite understand your idea to code-group instead if database grouping. How would I find groups for e.g. 10.000 records with hundred attributes?

 

Could you construct a small code sample, please.

 

I apreciate your help.

 

Regards

 

Robert
sfdcfoxsfdcfox

You'll probably need some sort of recursive map class:

 

public class sumclass {
    map<string,sumclass> subgroups;
    decimal total1, total2, ...;
    public sumclass() {
        subgroups = new map<string,sumclass>();
        total1 = total2 = ... = 0;
    }
}

 Then, from here, you'll have to build out the maps:

 

map<string,subclass> root = new map<string,subclass>();
for(obj__c c:[select ... from ... where ... ]) {
    if(!root.containsKey(c.groupfield1__c)) {
        root.put(c.groupfield1__c,new subclass());
    }
    if(!root.get(c.groupfield1__c).subgroups.containsKey(c.groupfield2__c)) {
        root.get(c.groupfield1__c).subgroups.put(c.groupfield2__c,new subclass());
    }
    root.get(c.groupfield1__c).subgroups.get(c.groupfield2__c).total1 += c.sumfield1__c;
    root.get(c.groupfield1__c).subgroups.get(c.groupfield2__c).total2 += c.sumfield2__c;
}

I expect about 150 lines per loop, though, so keep in mind the 200k script limit.