+ Start a Discussion
prageethprageeth 

Query doesn't return more than 200 records ???

Hi all;

I have the following Controller class and Page.

 Controller: 

public class MyClass {

public List<Opportunity> getOpportunityList() {

List<Opportunity> oppList = new List<Opportunity>();

for (Opportunity[] opp : [SELECT id, (SELECT id FROM OpportunityLineItems) FROM

Opportunity WHERE name LIKE '%ABC%']) {

for (Opportunity o : opp) {

oppList.add(o);

}

}

return oppList;

}

} 

  Page: 

<apex:page controller="MyClass">

<apex:repeat value="{!opportunityList}" var="op">

<apex:datatable value="{!op.OpportunityLineItems}" var="item">

<apex:column value="{!item.id}"/>

</apex:datatable>

<hr/>

</apex:repeat>

</apex:page> 

 My problem is, when the total number(SUM) of OpportunityLineItems in all of the Opportunities returned by my qyery is greator than 200, the VFPage shows an incorrect number of OpportunityLineItem rows.

 For an example if my query returns two Opportunities each has 100 LineItems, in the VF page I can Only see 101 LineItems.

Other LineItems are vanished. 

   When I use the following Syntax it works well. 

List<Opportunity> oppList = [SELECT id, (SELECT id FROM OpportunityLineItems) FROM Opportunity WHERE name LIKE '%ABC%'];

  But in my actual case It is not practical to use this method.

Can anybody explain the reason for the error. If the information supplied by me is not clear enough, I am ready to clarify further. Thanks.

 

 

Message Edited by prageeth on 11-22-2009 10:41 PM
NaishadhNaishadh

DML statements can only process up to 200 records at a time, and sObject list for

loops process records in batches of 200.

 

Ref : http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_loops_for_SOQL.htm#kanchor133

prageethprageeth

Hello Naishadh;

Thanks for the quick reply. 

However it is difficult for me to understand how this limitation is valid for my case. Actually I do not do a DML Operation such as Delete, Update etc. On the other side my query doesn't return 200 Opportunities, instead it returns few Opportunities with LineItems more that 200.

Could you please calrify it.  

Thanks. 

 

NaishadhNaishadh

Are you sure you can use

List<Opportunity> oppList = [SELECT id, (SELECT id FROM OpportunityLineItems) FROM Opportunity WHERE name LIKE '%ABC%'];

 because if you have too many records it will give you queryException "Inline query has too many rows for direct assignment, use FOR loop".

 

I have checked using for loop and it is working fine for me. Here is code

for(List<Opportunity> oppList : [SELECT id, (SELECT id FROM OpportunityLineItems) FROM Opportunity WHERE name LIKE '%Test%']) { System.debug(oppList.size()); }

 

 

 

 

prageethprageeth

Hello Naishadh;  

Following code doesn't give me an error. I don't know why. 

List<Opportunity> oppList = [SELECT id, (SELECT id FROM OpportunityLineItems) FROM Opportunity WHERE name LIKE '%ABC%'];

  The total number of LineItems In my selected Opportunities are greator than 200 but less than 300. Sometimes the error you have mentioned will populate if the number of lineItems are greator than 1000(I assume so because a list has a limit of 1000).

In my case the size of the Opportunity list is correct but the number of LineItems appears on the page is incorrect.

Do you think that there is a limit of 200 for inner query.