Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SabrentSabrent 

value of filter criterion for field must be of type string and should be enclosed in quotes

IN: Dynamic SQOL, Dynamic Search

Based on Jeff Douglas's article Dynamic Search,

Can anyone please provide suggestions as to how to use 'IN' in a  dynamic SOQL

// runs the search with parameters passed via Javascript
        public PageReference runSearch() {



String billcode = Apexpages.currentPage().getParameters().get('billcode');

List<String> parts = billcode .split('\\,');
}

if (!billcode.equals(''))
          
                soql += ' and bill_Code__c IN '+parts+'';

In the search parameter if i pass billcode as BC23,BC42, this is the SOQL that is generated

select Name, Branch__c, F1__c, F2__c, F3__c from Obj__c Where Branch__c!=null and bill_Code__c IN (BC42,BC93) order by Branch__c asc limit 25   // quotes missing

but the search doesn't return anything. it needs to be IN ('BC42','BC93') // quotes for each passed value.
Best Answer chosen by Sabrent
GSBassoGSBasso
You'll have to post the code that gave you that error but the following both work:

String[] accountNumbers = new String[]{'1111'};
Account[] accounts = Database.query('SELECT Id, Name FROM Account WHERE AccountNumber IN :accountNumbers');
accounts = [SELECT Id, Name FROM Account WHERE AccountNumber IN :accountNumbers];

All Answers

GSBassoGSBasso
You seem to already be aware of the issue (namely that the individual values inside the parentheses of an IN clause must be in quotes).

Another problem is that when the number of elements in parts is large enough the string conversion won't actually return all the values.

I'm wondering why you can't just use parts directly in the query, as in:

Obj__c[] list = [SELECT Name, ... FROM Obj__c WHERE Branch__c != NULL AND bill_Code__c IN :parts];
SabrentSabrent
I had done as you suggested however i got an error

Only variable references are allowed in dynamic SOQL/SOSL.

If i do ,

soql += ' and bill_code__c IN  \''+ parts+'\'';    

my querty results in

select Name, Branch__c, F1__c, F2__c, F3__c from Obj__c Where Branch__c!=null and bill_Code__c IN '(BC42,BC93)' order by Branch__c asc limit 25


Basically in my search paramenters if  I pass BC42,BC93 I want all records where bill_Code__c IN (' BC42', 'BC93') 
 

ideally my Query would be

select Name, Branch__c, F1__c, F2__c, F3__c from Obj__c Where Branch__c!=null AND (bill_Code__c = 'BC42' OR bill_Code__c = 'BC42') order by Branch__c asc limit 25
GSBassoGSBasso
You'll have to post the code that gave you that error but the following both work:

String[] accountNumbers = new String[]{'1111'};
Account[] accounts = Database.query('SELECT Id, Name FROM Account WHERE AccountNumber IN :accountNumbers');
accounts = [SELECT Id, Name FROM Account WHERE AccountNumber IN :accountNumbers];
This was selected as the best answer
SabrentSabrent
Thanks . I got it to work.
SabrentSabrent
@ sushimi sushimi



/*

//This is how i got it to work
//creating a list of comma separated values
//  iterating over this list to add to another list so that i have individualized values
//when in the search parameter user enters val1,val2,val3, it is actually interpreted as one string i.e string = 'val1,val2,val3'
// by doing what i have done in my code, i  get 'val1', 'val2', 'val3'
//  Let me know if you need further help or need more explanation

*/

public with sharing class ClassNameController {

  public list<string> parts1 {get;set;}

public void runQuery() {
                try {
                 system.debug('see how the SOQL is printed:' +soql);
                        pbatransactions =Database.query(soql + ' order by ' + sortField + ' ' + sortDir + ' limit 1000 ');
                } catch (Exception e) {
                  ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, e.getMessage()));
                }
}
public PageReference runSearch() {
         
         
           String billcode = Apexpages.currentPage().getParameters().get('billcode');
        
            List<String> parts = billcode.split(',');
            system.debug('>>>>>>>>>>>>>> ' +parts);
            parts1 = new list<string> ();
            for(integer i =0;i<parts.size();i++){
            string squotesappended =parts[i];
               parts1.add(squotesappended );
            }

  soql = 'select Name, Branch__c, bill_Code__c from Obj__c Where Branch__c!=null ' ;


  if (!billcode.equals(''))
            soql += ' and bill_Code__c IN: parts1';


   runQuery();
            return null;
}

}
Anant KamatAnant Kamat
There is some correction required in the for loop.

string squotesappended =parts[i]; should be changed as below

string squotesappended ='\'' + parts[i] + '\'';