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

2 Answers 2

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.

4
  • 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, 2017 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, 2017 at 3:39
  • I am using string.join and that is working. Thanks for help.
    – CRM Guy
    Jul 6, 2017 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, 2017 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.

4
  • 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, 2017 at 1:01
  • You mean you are passing the query string in the queryLocator like method which accepts a String? Jul 6, 2017 at 1:03
  • yes.. i am passing the string on query locator method.
    – CRM Guy
    Jul 6, 2017 at 2:00
  • 1
    @CRMGuy Use the countryList variable, as written in this answer. No need to use a string.
    – sfdcfox
    Jul 6, 2017 at 2:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .