Apex Code Development You need to sign in to do that
Don't have an account?
kumara100 How to apply governor limit solution to Database.query(sql)
My initial code get error when sending more queries as "System.Exception: Too many SOQL queries: 101"
So I rearrange code avoid query within loop.But afterthat it doesn't get results.
Here I use Database.query(sql) for getting describe objects.
Basically I want to get result of Database.query() to List object
Initial code
String oppSOQL = 'SELECT ';
for (String fieldName : oppFieldsMap.keySet()) {
oppSOQL += fieldName + ',';
}
for (Integer i = 0; i < arrIds.size(); i++) {
String id = arrIds[i];
String fullSOQL = '';
String SOQLEnd = ' FROM Opportunity WHERE Id = \'' + id + '\'';
if (customObjectDescribedQuery != null && customObjectDescribedQuery != '') {
fullSOQL = oppSOQL + '(' + customObjectDescribedQuery + ')' + SOQLEnd;
} else {
fullSOQL = oppSOQL.substring(0, oppSOQL.length() - 1) + SOQLEnd;
}
sObject sObj = Database.query(fullSOQL);
o = (Opportunity)sObj;
opportunityList.add(o);
subTotal += o.EM_TotalBeforeSalesDiscount__c;
}
for (Opportunity opp : opportunityList) {
oppObj = new OpportunityWrapper(opp);
selectedOpportunityList.add(oppObj);
}
Rearrange Code
String oppSOQL = 'SELECT ';
for (String fieldName : oppFieldsMap.keySet()) {
oppSOQL += fieldName + ',';
}
List<Id> oppIds = new List<Id>{};
for (Integer k = 0; k < arrIds.size(); k++) {
oppIds.add(arrIds[k]);
}
String fullSOQL = '';
String SOQLEnd = ' FROM Opportunity WHERE Id IN: ' + oppIds;
if (customObjectDescribedQuery != null && customObjectDescribedQuery != '') {
fullSOQL = oppSOQL + '(' + customObjectDescribedQuery + ')' + SOQLEnd;
} else {
fullSOQL = oppSOQL.substring(0, oppSOQL.length() - 1) + SOQLEnd;
}
List<Opportunity> oppList = Database.query(fullSOQL); //new List<Opportunity>(Database.query(fullSOQL));
for(Opportunity o :oppList ){
opportunityList.add(o);
subTotal += o.EM_TotalBeforeSalesDiscount__c;
}
for (Opportunity opp : opportunityList) {
oppObj = new OpportunityWrapper(opp);
selectedOpportunityList.add(oppObj);
}
Hi, first, you can never have a SOQL into a FOR, because if the FOR execute more than 100 times you hit the too many soql limit. Second, you can only store up 1000 records in a List, so... if you want to store more than 1000 in memory you need to use a List<List<Opportunity>> and a FOR Loop. With this list of lists you can store up to 10.000 records (not 1.000.000 = 1000 * 1000, because you hit the retrieved rows by a SOQL limit first...).
Your code going to look like this :
List<List<Opportunity>> opptyList = new List<List<Opportunity>>(); List<Opportunity> tempList = new List<Opportunity>(); for (Opportunity iter : Database.query(fullSOQL)) { tempList.add(iter); if (tempList.size() == 1000) { opptyList.add(tempList); tempList.clear(); } } if (tempList.size() > 0) { opptyList.add(tempList); tempList.clear(); }
I hope this help you.
Regards,
J.
Hey
In this situation it's very helpful to put system.debug() messages at key points, eg.
String SOQLEnd = ' FROM Opportunity WHERE Id IN: ' + oppIds;
System.debug('SOQLEnd: '+SOQLEnd);
And then a bit later at,
System.debug('fullSOQL: '+fullSOQL);
List<Opportunity> oppList = Database.query(fullSOQL);
Common mistakes include missing quotation marks, but I've briefly looked at your code and I suspect your issue most likely on the line:
String SOQLEnd = ' FROM Opportunity WHERE Id IN: ' + oppIds;
Check your logs and let me know what the output is giving you.
Cheers,
Wes
Sorry, JmBessonart. I don't mean to inturrupt, but in your sample, tempList reference is still alive after putting to opptyList so that opptyList will have a banch of empty lists. So, it needs to be clone or new list rather than just clear.
ThomasTT
Tx ur reply.
Ur assume is correct. I checked further with System.debug().
But still can't get results.
My conclusions as follows.
String fullSOQL = 'SELECT Amount, Id, Name FROM Opportunity WHERE Id IN ' + oppIds;
for(Opportunity o : Database.query(fullSOQL)){...}
1) when 'fullSOQL' query string has '(' or '[' it gives exception as "unexpected token: '('"
fullSOQL = SELECT Amount, Id, Name FROM Opportunity WHERE Id IN (00690000002LdSrAAK, 00690000002LdSvAAK, 00690000002LdT0AAK)
this ids came from oppIds and it is List. So when exectue Database.query(fullSOQL)) it goes like ....(id1,id2, id3, ....)
2) SELECT Amount, Id, Name, [SELECT pricebookentryid,PricebookEntry.Product2.Name FROM OpportunityLineItems] FROM Opportunity WHERE Id IN (00690000002LdSrAAK, 00690000002LdSvAAK, 00690000002LdT0AAK) gives same exception as "unexpected token: '['"
3) SELECT Amount, Id, Name, (SELECT pricebookentryid,PricebookEntry.Product2.Name FROM OpportunityLineItems) FROM Opportunity WHERE Id IN (00690000002LdSrAAK, 00690000002LdSvAAK, 00690000002LdT0AAK) gives same exception as "unexpected token: '('"
4) But fullSOQL = SELECT Amount, Id, Name FROM Opportunity WHERE Id = '00690000002LdSrAAK' . It works perfectly.
try{
String fullSOQL = '';
System.debug('============fullSOQL:========= ' + fullSOQL);
for(Opportunity opp : Database.query(fullSOQL)){
System.debug('=========== for(Opportunity o =============== ' + opp);
opportunityList.add(opp);
}
} catch (Exception e) {
System.debug('============Exception e:========= ' + e.getMessage());
}
So my conclusions is, when query string has '(' , '[', SELECT within SELECT, Database.query(query)) method doesn't get results for,
for(Opportunity opp : Database.query(query)){ ...}
but it works for,
sObject sObj = Database.query(query); within FOR loop (one OpportunityId at once)
What about ur ideas Wes....
Any other comments
Hey buddy
I've just typed this up as an anonymous block script and it's given me results:
String qs = 'SELECT id FROM Account WHERE id IN (\'001A0000001opNCIAY\',\'001A0000001opNDIAY\')';
for(Account acc: database.query(qs)){
System.debug(acc);
}
I think it might be throwing you a red herring with that error message. I'm sure you just need to quote your ids. Use the above syntax to escape your quotes and then give it another bash.
Wes