How to solve this: Multiple IN condition in SOQL - System.QueryException: Only variable references are allowed in dynamic SOQL/SOSL

Question

Hi am getting an following error while running dynamic soql. how to over come this when we use multiple IN condtion in soql query dynamically

code:

     String docid = 'hi asma US6775512356';
           List<String> lstString = docid.split(' ');
list<string> lstsub = new list<string>();
for(string s:lstString)
{
   lstsub.add(s);
}
   string IDF_QuerySelect;
           string wherefind;
system.debug('lstString>>'+lstString);
List<Incoming_Email_Config_Rule__c> Patent_mail= new list<Incoming_Email_Config_Rule__c>();
               Patent_mail =[SELECT Match_Email_To__c, Subject_Contains_Field__c, Sender__c FROM Incoming_Email_Config_Rule__c WHERE Match_Email_To__c ='SymphonyIPM__Patent__c'];
               list<EmailMessage> lstmsgs = new list<EmailMessage>();

               integer j=0;
               system.debug('j>>'+j);
               for(Incoming_Email_Config_Rule__c patemail :Patent_mail)
               {
                   IDF_QuerySelect=+','+patemail.Subject_Contains_Field__c;
                   if(j==0)
                   {
                       wherefind=+patemail.Subject_Contains_Field__c+ ' IN: '+lstsub;
                       system.debug('wherefind>>>'+wherefind);
                   }
                   else
                   {
                       system.debug('wherefind else part:>>'+wherefind);
                       wherefind+=' OR '+patemail.Subject_Contains_Field__c+' IN: '+lstsub;
                       system.debug('wherefind else part:>>'+wherefind);
                       //assetClause += ' OR ' + field + ' != NULL ';
                   }
                   j=j+1;
               }


               string query1 ='Select id'+IDF_QuerySelect+' from SymphonyIPM__Patent__c where '+wherefind;
               system.debug('query1>>>>>'+query1);
               List<SymphonyIPM__Patent__c> PAT_Result = Database.query(query1);

Error as: System.QueryException: Only variable references are allowed in dynamic SOQL/SOSL. Query output as:

>>>>>Select id,SymphonyIPM__Application_Number__c from SymphonyIPM__Patent__c where Name IN: (hi, asma, US6775512356) OR SymphonyIPM__Application_Number__c IN: (hi, asma, US6775512356)

Answer 1

You use : for binding a variable. However, that's not what you're doing; you're trying to construct the string manually. You'll need to fix your code to add the quotes and such:

wherefind=+patemail.Subject_Contains_Field__c+ ' IN (\''+String.join(lstsub,'\',\'')+'\')';