I have a following method which returns result based on the dynamic SOQL query. It was running fine until I added additional filter criteria where I am checking Id != : ClosedCleanupId Below is the method

 public static List < sObject > fetchLookUpValues(String searchKeyWord, String ObjectName, String ClosedCleanupId) {
   // system.debug('ObjectName-->' + ObjectName);
    Id closedId = Id.valueOf(ClosedCleanupId);
    set<Id> setclosedId = new set<Id>();
    String searchKey = searchKeyWord + '%';
    Set<String> closedStages = new set<String> {'Closed - Clean Up','Closed - Lost'};
    List < sObject > returnList = new List < sObject > ();
    List<sObject> searchResult = new List<sObject> ();
    String sQuery =  'select id, Name,Deal_Registration_Id__c from ' +ObjectName + ' where id NOT IN : setclosedId AND stageName NOT IN :closedStages AND Deal_Registration_Id__c LIKE: searchKey order by createdDate DESC limit 5';
    List < sObject > lstOfRecords = Database.query(sQuery);
    for (sObject obj: lstOfRecords) {
    return returnList;

Can anyone point me what's the issue with dynamic query?

EDIT 1:- It seems value of ClosedCleanupId is in double quotes , like "006..." , So Id.valueOf(ClosedCleanupId) throws error. Is there a way I can replace double quotes in Apex or in JS? This method is auraenabled and called from lightning component JS controller.


2 Answers 2


In Dynamic SOQL, you need to pay attention to spaces. There should be no space between : and what follows.


id NOT IN : setclosedId 


id NOT IN :setclosedId 

Spacing is important when building dynamic query strings, as the system is far less lenient in that case.

  • 5
    Never knew that. Must have been consistent/lucky up to now.
    – Keith C
    Dec 9, 2019 at 20:03
  • I am still getting the error. It seems value of String ClosedCleanupId is in double quotes like - "006... " , that's the reason Id.valueOf(ClosedCleanupId) returns error. To be more specific to my query- Is there a way I can convert double quotes to single quotes in apex? ClosedCleanupId value is getting from JS controller method which has string type of the attribute Dec 9, 2019 at 21:00
  • @sfdcfox I've just tested but even with a space my dynamic query runs without error
    – dibocor
    Dec 10, 2019 at 0:03
  • @dibocor Odd. I remember that being a thing. Maybe it was something else I'm thinking of, or maybe they fixed it. Hard to tell, at this point.
    – sfdcfox
    Dec 10, 2019 at 4:18
  • @ManishAnand Your ID value shouldn't have quotes in it. Perhaps you should approach this from the other direction: why are there quotes in your ID value? There's something probably wrong with the JS, not the Apex.
    – sfdcfox
    Dec 10, 2019 at 4:18

How about just leaving the ids as strings? Something like:

public static List <sObject> fetchLookUpValues(String searchKeyWord, String objectName, String closedCleanupId) {
  String searchKey = searchKeyWord + '%';
  Set<String> closedStages = new set<String> {'Closed - Clean Up','Closed - Lost'};
  String sQuery =  'SELECT Id, Name,Deal_Registration_Id__c FROM ' + objectName + ' WHERE Id != :closedCleanupId AND stageName NOT IN :closedStages AND Deal_Registration_Id__c LIKE :searchKey ORDER BY createdDate DESC LIMIT 5';
  return Database.query(sQuery);

This method could probably benefit from some user input checking and perhaps try to examine the result of the query, but this would be a start.

You must log in to answer this question.

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