1

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.

2

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 ...]
3
  • 1
    One quibble, you can get the query count using Integer recordCount = [SELECT count() FROM Lead WHERE ...]. You do not need to query all the records and call .size() on a list.
    – Adrian Larson
    Dec 29 '16 at 16:21
  • Yes, you're rigth Adrian ! I add this to my post. Thanks !
    – SF_user
    Dec 30 '16 at 8:39
  • Worth mentioning that the restriction applies also to binding variables who refer to a static or instance variable of an Apex class. In other words, the error Variable does not exist, will be triggered also for the following query: List<sObject> sobjList = Database.query('SELECT Id FROM MyCustomObject__c WHERE field1__c = :MyClass.myPublicStaticVariable'); Sep 21 '18 at 13:32
1

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.

2
  • This was exactly my issue, thanks. Aug 11 '19 at 14:38
  • Do you know if there's a solution to this problem? if we want to create a helper class to use dynamic queries, how do we make this variables available to that class? Nov 18 '19 at 10:07

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.