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 Answers 2

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
    Commented Dec 29, 2016 at 16:21
  • Yes, you're rigth Adrian ! I add this to my post. Thanks !
    – SF_user
    Commented Dec 30, 2016 at 8:39
  • 1
    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'); Commented Sep 21, 2018 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. Commented Aug 11, 2019 at 14:38
  • 2
    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? Commented Nov 18, 2019 at 10:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .