System.QueryException: expecting a colon, found New

Question

 String emailBlastQuery = 'SELECT Id, Inquiry_Type__c, Product_type__c, Solution__c, Reason_For_Calling__c, '+
                                'Problem_Category__c, Duplicate_Case_Problem_Category__c,Duplicate_Case_Product_Type__c, '+
                                'Duplicate_Case_Reason_For_Calling__c, Duplicate_Case_Status__c,Web_Product_Business_Group__c, '+
                                'WebProductPLTypeName__c,Age_Hours__c,Language__c, Case_Status__c,Completed_Date__c, '+
                                'status__c, No_Of_Emails_Sent__c, No_Of_Duplicate_Cases__c '+
                                'FROM  Email_Blast__c '+
                                'WHERE status__c = '+System.Label.Email_Blast_Process_Status+
                                ' AND Criteria_Tested__c = ' + System.Label.Email_Blast_TestingStatus +
                                ' AND No_Of_Records_Satisfying_Criteria__c > '+System.Label.Email_Blast_Case_Count_Limit;

 System.debug('hello'+emailBlastQuery);
 return Database.getQueryLocator(emailBlastQuery);

Answer 1

Seems quite likely that the value of System.Label.Email_Blast_Process_Status is New. When building a dynamic query, you must wrap string values in escaped single quotes. You should also escape the Label itself.

'WHERE Field__c = \' + String.escapeSingleQuotes(Label.MyLabel) + ''\'

However, you don't even need to use a dynamic query here, so you should not. Always prefer a static query where one will work.

Database.query([
    SELECT ...
    FROM MyObject__c
    WHERE Field__c = :Label.MyLabel
]);

Answer 2

For completeness, in cases where dynamic SOQL is necessary, the : binding prefix still can be used but is limited to simple values (no . or method invocations). So taking Adrian's example this could be done:

String myLabel = Label.MyLabel;

'WHERE Field__c = :myLabel'

with the advantage that the various data types are handled correctly automatically, that the code is easier to read and that the SOQL injection risk is automatically addressed.

But as Adrian recommends, always go for static SOQL if you can as:

• The query is easier to read - no quotes needed - and can be broken up over multiple lines to help with that readability without having to add string concatenation noise
• The compiler will check the syntax of the query and that the field and object names are correct (rather than you having to wait for a runtime error when you try the code)
• The : binding supports complex expressions (. and methods invocations)

I would write the query like this:

return [
    SELECT
        Id,
        Inquiry_Type__c,
        Product_type__c,
        Solution__c,
        Reason_For_Calling__c,
        Problem_Category__c,
        Duplicate_Case_Problem_Category__c,
        Duplicate_Case_Product_Type__c,
        Duplicate_Case_Reason_For_Calling__c,
        Duplicate_Case_Status__c,
        Web_Product_Business_Group__c,
        WebProductPLTypeName__c,
        Age_Hours__c,
        Language__c,
        Case_Status__c,
        Completed_Date__c,
        status__c,
        No_Of_Emails_Sent__c,
        No_Of_Duplicate_Cases__c
    FROM Email_Blast__c
    WHERE status__c = :Label.Email_Blast_Process_Status
    AND Criteria_Tested__c = :Label.Email_Blast_TestingStatus
    AND No_Of_Records_Satisfying_Criteria__c > :Label.Email_Blast_Case_Count_Limit
];