1

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>();
    setclosedId.add(closedId);
    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) {
       returnList.add(obj);
    }
    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.

1

2 Answers 2

8

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

Bad

id NOT IN : setclosedId 

Good

id NOT IN :setclosedId 

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

6
  • 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
0

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 .