0

When i have executed my code i got the exception System.QueryException: Only variable references are allowed in dynamic SOQL/SOSL. . Kindly assist me.

string query3 ='select id, accountId, UserOrGroupId, AccountAccessLevel, Account.Products_Serviced_by_GFL__c, Account.RecordType.Name 
                  from AccountShare where id =:SharedAccIds ';
query3= query3+'AND accountId =:(select id from account where Products_Serviced_by_GFL__c includes ('+prodType+') AND RecordType.Name IN :regionlist)';
list<AccountShare>aftersharedAcclist=database.query(query3);
system.debug('aftersharedAcclist: '+aftersharedAcclist);
5
  • Is there no indication of the position of the error in the message you get? I'm thinking it may be the ":" character here "AND accountId =:(select" but without seeing the full error it's difficult to know. salesforce.stackexchange.com/questions/121537/… this may be helpful as reference Oct 6 '20 at 13:35
  • Is SharedAccIds is a collection ? If so Use ' ... Id IN:'+SharedAccIds
    – Sarvesh
    Oct 6 '20 at 13:36
  • @Sarvesh, Yes it is a collection. Oct 6 '20 at 13:42
  • @Matthew Whisker, Complete exception is : USER_DEBUG [199]|DEBUG|Exception: System.QueryException: Only variable references are allowed in dynamic SOQL/SOSL. Oct 6 '20 at 13:43
  • @Ankitverma, ok, have tried the above my comment.
    – Sarvesh
    Oct 6 '20 at 13:52
2

I don't see why this needs to be a dynamic query, and if there isn't a good reason for it to be dynamic, you should just make it a static query (i.e. use [SELECT Id FROM Object WHERE Filters] instead of building your query as a string and using Database.query()).

The main benefit of dynamic queries is that it allows you to dynamically determine things (fields to query (which could be defined by a fieldset), the object, the filters), but you're not making use of any of that.

That said, your query syntax has a few basic errors.

From what I can see, the issues are all on your second line query3 = query3 + 'AND accountId =:(select id from account where Products_Serviced_by_GFL__c includes ('+prodType+') AND RecordType.Name IN :regionlist)';

  • You could use query3 += <string> instead of query3 = query3 + <string>
  • The colon : is only used for variable binds, your use of it with your semi-join is incorrect (AND field = :(semi-join) is not valid syntax, it's just AND field = (semi-join))
    • ^ This one is probably the root of your error message
  • INCLUDES is an operator specifically for multi-picklists, double check to ensure Products_Serviced_by_GFL__c is indeed a multi-picklist
  • If prodType is a collection, you'd want to do some processing (prior to your query) to turn it into a string

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.