System.QueryException: Variable does not exist

Question

I am using dynamic SOQL and trying to get the count of a query result but I am getting an exception. I have done some research to find a solution and found that it isn't possible to use complex bind variables in dynamic SOQL yet.

count  = database.countQuery('Select Id from Lead where Admission_Status__c IN : setStatus and Intake_Year__c =:objLead.Intake_Year__c and Intake_Month__c =:objLead.Intake_Month__c and Program__c=:objLead.Program__c');                   

So can anybody confirm that my understanding is correct? For now I am going to achieve the count with static SOQL and using the size function.

Answer 1

The doc says :

You can use simple bind variables in dynamic SOQL query strings. The following is allowed:

String myTestString = 'TestName';
List<sObject> sobjList = Database.query('SELECT Id FROM MyCustomObject__c WHERE Name = :myTestString');

However, unlike inline SOQL, dynamic SOQL can’t use bind variable fields in the query string. The following example isn’t supported and results in a Variable does not exist error:

MyCustomObject__c myVariable = new MyCustomObject__c(field1__c ='TestField');
List<sObject> sobjList = Database.query('SELECT Id FROM MyCustomObject__c WHERE field1__c = :myVariable.field1__c');

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_dynamic_soql.htm

So, you're right, you can query to get a List and then test his size.

Or, like says Adrian Larson, you can use count() in your query :

Integer recordCount = [SELECT count() FROM Lead WHERE ...]

Answer 2

Another reason why this may happen is because the variable you're binding is out of scope to where the query is actually executed. Take the following method for example:

public static List<OrderItem> queryOrderItems(String filter) {
    String query = 'SELECT ';

    for (Schema.FieldSetMember f : SObjectType.OrderItem.FieldSets.Order_Item_Query_Field_Set.getFields()) {
        query += f.getFieldPath() + QUERY_SEPARATOR;
    }

    query = query.substring(0, query.length() - 1);
    query += ' FROM OrderItem';

    if (!String.isBlank(filter)) {
        query += ' WHERE ' + filter;
    }

    return Database.query(query);
}

And call the method like so:

Set<Id> oiIds = rows.keySet();

for (OrderItem oi : QueryGenerator.queryOrderItems('Id IN :oiIds ORDER BY OrderItemNumber ASC')) {
    //do stuff
}

You will receive the error message Variable does not exist: oiIds. This is due to the scope of the oiIds variable and when it gets dynamically bound, which is not where the query is executed.