9

Declared a string and assigned SOQL value as shown below. While running I am getting an error.

String country = 'United States';
String accountQuery = 'SELECT Id,BillingPostalCode,County_Name__c '+
     'FROM Account WHERE zipCodeLen__c =9 AND AND BillingCountry = '+ Country;

System.QueryException: unexpected token: 'AND'

System.QueryException: expecting a colon, found 'United'enter image description here

  • 1
    The keyword 'AND' occurs twice! – Geert Immerzeel May 13 '15 at 9:20
13

Any time you use a literal String in a query, the value must be wrapped in single quotes. So in addition to removing the double AND, you need to wrap your value in quotes. If you debug the query, you will probably see

SELECT Id, BillingPostalCode, County_Name__c
FROM Account
WHERE ZipCodeLen__c = 9
AND AND BillingCountry = United States

Instead you should wrap it in quotes and escape it.

String accountQuery = 'SELECT Id, BillingPostalCode, County_Name__c '+
    'FROM Account WHERE ZipCodeLen__c = 9 AND BillingCountry = \''
    + String.escapeSingleQuotes(Country) + '\'';

Which should yield:

SELECT Id, BillingPostalCode, County_Name__c
FROM Account
WHERE ZipCodeLen__c = 9
AND BillingCountry = 'United States'

Try running them both in the query editor in the dev console.

10

If you use simple bind variables in dynamic SOQL escaped quotes can be avoided (as can any danger of SOQL injection attack):

String country = 'United States';
String soql = ''
        + ' SELECT Id, BillingPostalCode, County_Name__c'
        + ' FROM Account
        + ' WHERE zipCodeLen__c = 9'
        + ' AND BillingCountry = :country'
        ;
for (Account a : Database.query(soql)) {
    ....
}

PS

By the way, although the example in the documentation for batch Apex uses dynamic SOQL you certainly don't have to; the reasons to choose static or dynamic SOQL are unrelated to that context. So better still:

public Database.QueryLocator start(Database.BatchableContext bc) {
    String country = 'United States';
    return Database.getQueryLocator([
            SELECT Id, BillingPostalCode, County_Name__c
            FROM Account
            WHERE zipCodeLen__c = 9
            AND BillingCountry = :country
            ]);                                          
}
  • It does make your code more brittle though because you can introduce a runtime error (instead of compile fail) by changing variable names. :( – Adrian Larson May 12 '15 at 17:33
  • @AdrianLarson Fair point; with dynamic SOQL a test is going to be needed anyway. – Keith C May 12 '15 at 17:38
  • Yes all queries should be tested. But assertion messages are much easier to understand. I have definitely pursued both approaches, just pointing out that drawback. Another I have noticed is that the query becomes context dependent. I can't copy paste that into query editor and see what I get. I still gave you +1; both approaches deserve mention! – Adrian Larson May 12 '15 at 17:43
3

In addition to previous answers, dynamic queries can be a headache sometimes with this kind of issues related to runtime exceptions. Also they can be hard to maintain. If you want to avoid possible issues like this in the future you can try using lists and string functions to generate your dynamic queries so it can be easier to track and maintain. For example in your case you can use:

String country = 'United States';

List<String> selectFieldsList = new List<String>{
    'Id' , 'BillingPostalCode', 'County_Name__c'
};

List<String> whereConditionsList = new List<String>{
    'zipCodeLen__c = 9',
    'BillingCountry =: country'
};

String selectFields = String.join(selectFieldsList, ', ');
String whereConditions = String.join(whereConditionsList, ' AND ');

String accountQuery = String.format('SELECT {0} FROM Account WHERE {1}', new List<String>{selectFields, whereConditions});

It is a little bit more verbose, but better for maintenance since you can ensure a correct syntax in your query. Even more, you can leverage the Java best practice for string concatenation here but that is another matter. Hope it helps.

1

Assuming that "zipCodeLen__c" is a number field, try below string,

String accountQuery = 'SELECT Id,BillingPostalCode,County_Name__c '+
          'FROM Account WHERE zipCodeLen__c = 9 AND BillingCountry LIKE \'%'+ country;
1

You have an extra AND in the Query. Enclosed in asterisks in below query.

String country = 'United States';
String accountQuery = 'SELECT Id,BillingPostalCode,County_Name__c '+
     'FROM Account WHERE zipCodeLen__c =9 AND ***AND*** BillingCountry = '+ Country;

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.