0

I am using a list in my soql, the list is formed with different names of countries from set of seperate records. The list(name - countryList) is of format.

    List<ABC__c> rlList = ABC__c.getall().values();
    Set<String> countryList = new Set<String>(); 
    for (ABC__c pr : rlList){     
        String countrywithquotes = '\''+ pr.Country__c + '\'';
        countryList.add(countrywithquotes);

    }

countryList - {'India', 'USA','UK','China','Brazil'}

Query String in Apex Code -

String qry = 'Select  Id, country__c from BDC__c where Country__c IN ('+ countryList +') '

In debug log , the SOQL query string produces a query like below -

Select  Id, Country__c  from ABC__c where Country__c  IN ({'India', 'USA','UK','China','Brazil'})

Please let me know a way to remove the extra { character so that the SOQL can be executed.

2

You can simply do:

List<ABC__c> rlList = ABC__c.getall().values();
    Set<String> countryList = new Set<String>(); 
    for (ABC__c pr : rlList){     
        if(!string.isBlank(pr.Country__c)) countryList.add(pr.Country__c);

    }

String query = 'Select Id From ABC__c Where Country__c In :countryList';

as dynamic queries allow bind variables

otherwise, as an exercise in learning String methods You can do something like this the generate the string you need when you cannot use a bind variable:

List<ABC__c> rlList = ABC__c.getall().values();
    Set<String> countryList = new Set<String>(); 
    for (ABC__c pr : rlList){     
        String countrywithquotes = '\''+ pr.Country__c + '\'';
        countryList.add(countrywithquotes);

    }

String query = 'Select Id From ABC__c Where Country__c In (' + 
        string.join(New List<String>(countryList),',') + ')';

This will concat the Countries (that already have quotes around them) with a comma

Depending on your idea of simplification and code placement you could also do this:

List<ABC__c> rlList = ABC__c.getall().values();
    Set<String> countryList = new Set<String>(); 
    for (ABC__c pr : rlList){     
        countryList.add(pr.Country__c);

    }

String query = 'Select Id From ABC__c Where Country__c In (\'' +
                 string.join(New List<String>(countryList),'\',\'') + '\')';

As an aside, not sure what you mean by the comment

My query in batch class is too big so can not be fit into a single line

As a query does not have to fit into a single line. The below is perfectly valid

[
   Select
        Id,
        Name
   From Account
]

As long as it is within the brackets. Additionally a dynamic string can be on multiple lines as well.

| improve this answer | |
  • I followed the 1st approach - List<ABC__c> rlList = String query = 'Select Id From ABC__c Where Country__c In :countryList'; However, getting the error for {} and in debug log i see my query contains country list like -{'India', 'USA','UK','China','Brazil'} – CRM Guy Jul 6 '17 at 1:51
  • @CRMGuy - Did you modify the for loop like it have it? If so update your question with your current attempt cause I am confident that the code I posted works just fine – Eric Jul 6 '17 at 3:39
  • I am using string.join and that is working. Thanks for help. – CRM Guy Jul 6 '17 at 3:48
  • @CRMGuy - The first example should work just fine as well and is the better pattern. The others are kinda anti patterns of sorts – Eric Jul 6 '17 at 3:49
2

Do it in this way:

for(ABC__c abc: [Select  Id, country__c from ABC__c where Country__c IN: countryList]){
  //process abc record here
}

If you concatenate a List in a string then you will always get the {item1, item2, ...} so you should go for the variable binding.

| improve this answer | |
  • My query in batch class is too big so can not be fit into a single line . Hence using query string. Any suggestions? – CRM Guy Jul 6 '17 at 1:01
  • You mean you are passing the query string in the queryLocator like method which accepts a String? – itzmukeshy7 Jul 6 '17 at 1:03
  • yes.. i am passing the string on query locator method. – CRM Guy Jul 6 '17 at 2:00
  • 1
    @CRMGuy Use the countryList variable, as written in this answer. No need to use a string. – sfdcfox Jul 6 '17 at 2:32

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.