What's wrong with this SOQL

Question

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'

Answer 1

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.

Answer 2

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
            ]);                                          
}

Answer 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.

Answer 4

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;

Answer 5

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;