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
hisrinuhisrinu 

Problem in Dynamic SOQL

Code:
public class List1 
{
    Public void abc()
    {
        List<Account> acc = new List<Account>();
        List<String> IdList = new List<String>();
        List<Contact> ConList = new List<Contact>; 
        acc = [select Id from Account];
        for(Account a : Acc)
        {
            String temp = '\''+a.Id+'\'';
            IdList.add(temp);
        }
        String s = 'select id from Contact where Account.id in '+IdList;
        ConList = Database.query(s);
    }
}

While executing the above dynamic SOQL query, I am getting the following error. System.QueryException: unexpected token: .

If my Account  query is returning <= 10 then its working fine, but if it returns more than 10 then I am getting the error.

Any suggestions on this are highly appreciated.


Note: I need to execute this as Dynamic SOQL not the static SOQL.



 



Message Edited by hisrinu on 12-29-2008 04:29 AM
Best Answer chosen by Admin (Salesforce Developers) 
WilmerWilmer
Hi, I have some suggestions i hope could help you.

  1. Add a LIMIT to your query to Accounts if you have large data. Remember that object support only 1000 elements.
  2. Build an string of Account Ids, don´t send the list directly.
Code:
// Allows to build a string like ('a', 'b', 'etc') emulating the format used in a normal query. 
 public static string fn_getStringIds(list<id> IdsToConcatenate_List){
  string strReturnedIds = '(';
  for(id idItem : IdsToConcatenate_List){
   if(strReturnedIds.length() > 1) // If strReturnedIds is not empty, adds a comma as separator before adding new values.
    strReturnedIds += ','; 
   strReturnedIds += '\'' + idItem + '\'';
  }
  strReturnedIds += ')';
  return strReturnedIds;
 }

You could call that function like this:
 
Code:
string strIds = '';
acc = [select Id from Account LIMIT Your_VALUE];
strIds = fn_getStringIds(acc);
String s = 'select id from Contact where Account.id in '+strIds;
ConList = Database.query(s);

 I hope this helps.

Wilmer





 

All Answers

WilmerWilmer
Hi, I have some suggestions i hope could help you.

  1. Add a LIMIT to your query to Accounts if you have large data. Remember that object support only 1000 elements.
  2. Build an string of Account Ids, don´t send the list directly.
Code:
// Allows to build a string like ('a', 'b', 'etc') emulating the format used in a normal query. 
 public static string fn_getStringIds(list<id> IdsToConcatenate_List){
  string strReturnedIds = '(';
  for(id idItem : IdsToConcatenate_List){
   if(strReturnedIds.length() > 1) // If strReturnedIds is not empty, adds a comma as separator before adding new values.
    strReturnedIds += ','; 
   strReturnedIds += '\'' + idItem + '\'';
  }
  strReturnedIds += ')';
  return strReturnedIds;
 }

You could call that function like this:
 
Code:
string strIds = '';
acc = [select Id from Account LIMIT Your_VALUE];
strIds = fn_getStringIds(acc);
String s = 'select id from Contact where Account.id in '+strIds;
ConList = Database.query(s);

 I hope this helps.

Wilmer





 
This was selected as the best answer
hisrinuhisrinu
Hi Wilmer,

  Thanks for your reply, its working fine.
   Thanks a lot.
hisrinuhisrinu
Hi Wilmer,
 
 one more question do you have any idea on the max length of string data type.
WilmerWilmer
You mean the built string for the dynamic query? It is 10.000 characters.
hisrinuhisrinu
Not the built string, I am asking regarding the max length of string data type.
I hope it is 32000 characters?