+ Start a Discussion
SubratSubrat 

Dynamic SOQL Query Help

HI All,
Thanks in advance!! 
I have created visualforce page & displaying result dynamically below is the controller of my class:
public with sharing class DoctorsListdata1 {


    public Myforce_Campaign__c cmpg{get;set;}
    public Myforce_Campaign__c cmpg1{get;set;}
    public Patch_Customer__c pc{get;set;}
    public List<Patch_Customer__c> selectedAccounts{get;set;}
     public Id cmpId;
   public Decimal maxLimit{get;set;}
     
    public DoctorsListdata1(ApexPages.StandardController controller) {
        this.cmpg = (Myforce_Campaign__c)controller.getRecord();
         cmpId = ApexPages.CurrentPage().getParameters().get('id');
        cmpg.Id = apexpages.currentpage().getparameters().get('Id');
        system.debug('Plan ID' +cmpg.Id);
        
        cmpg1 = [select id, name,Speciality__c,Class__c,Visit_Frequency__c,Max_Doctor_Participation_Count__c,Min_Doctor_Participation_Count__c FROM Myforce_Campaign__c WHERE Id=:cmpId];
        maxLimit = cmpg1.Max_Doctor_Participation_Count__c;
        pharmaProductList();
    }
    
   Public List<PatchCusWrapper> productList{get;set;}
   public List<PatchCusWrapper> pharmaProductList(){
         productList = new List<PatchCusWrapper>();
         
         list<String> Speciality = new list<String>();
         
         system.debug(cmpg1);
         system.debug(cmpg1.Speciality__c);
         if(cmpg1.Speciality__c <> null)
         
         for(String str : cmpg1.Speciality__c.split(';'))
            {
            String lpmstr = '\'' +str+'\'';
            String lpm1 = '';
            lpm1 = lpm1 + lpmstr;
               Speciality.add(lpm1);


            }

List<String> Class1 = new List<String>();
for(String str : cmpg1.Class__c.split(';'))
            {
            String lpmstr = '\'' +str+'\'';
            String lpm1 = '';
            lpm1 = lpm1 + lpmstr;
               Class1.add(lpm1);


            }
list<String> Frequency = new list<String>();
for(String str : cmpg1.Visit_Frequency__c.split(';'))
            {
            String lpmstr = '\'' +str+'\'';
            String lpm1 = '';
            lpm1 = lpm1 + lpmstr;
               Frequency.add(lpm1);


            }



string Doctor='Doctor';
string status='Approved';
List<Patch_Customer__c> pcList = new List<Patch_Customer__c>();
String query='Select Id,First_Name__c,Last_Name__c,Patch__c,Patch__r.Name,Speciality__c,Graduation__c,Class__c,Frequency__c,City__c, Hospitals_Name__c from Patch_Customer__c WHERE RecordType.Name = \'' +Doctor+'\' ';


        if(cmpg1.Speciality__c !=null ){
            cmpg1.Speciality__c= cmpg1.Speciality__c.replace(';', ',');
            query +=  ' AND Speciality__c IN ' +Speciality;
        }
        
        if(cmpg1.Class__c !=null ){
            query +=  ' AND Class__c IN ' +Class1;
        }
        
        if(cmpg1.Visit_Frequency__c !=null ){
            query +=  ' AND Frequency__c IN ' +Frequency;
        }
         query += '  LIMIT 1000';
system.debug(query);
        pcList= Database.query(query);
         
             // List<Patch_Customer__c> pcList = [Select Id,First_Name__c,Last_Name__c,Patch__c,Patch__r.Name,Speciality__c,Graduation__c,Class__c,Frequency__c,City__c, Hospitals_Name__c from Patch_Customer__c WHERE RecordType.Name = 'Doctor' limit 5];
             system.debug(pcList);
             
             List<Id> customerList = new List<Id>();
                Map<Id, Patch_Customer__c> customerMap = new Map<Id, Patch_Customer__c>();
                for(Patch_Customer__c p : pcList){
                    customerList.add(p.Id);
                    customerMap.put(p.Id, p);
                }
   List<Campaign_Data__c> cdList = [SELECT Id, Customer__c FROM Campaign_Data__c WHERE Myforce_Campaign__c =:cmpg.Id ];
          
             Map<Id, Campaign_Data__c> cdMap = new Map<Id, Campaign_Data__c>();
             for(Campaign_Data__c b : cdList){
                 cdMap.put(b.Customer__c, b);
             }
             for(Id c : customerList){
             
                 if(cdMap.get(c) !=null){
                     productList.add(new PatchCusWrapper(cdMap.get(c),customerMap.get(c), true)); 
                 }
                 else{
                     Campaign_Data__c ppp = new Campaign_Data__c();
                     productList.add(new PatchCusWrapper(ppp, customerMap.get(c), false)); 
                }
             }
             system.debug('sdfkgfdgk jdfg hj');
         system.debug(productList);
         return productList;
     }

The above query is working fine when even I am selecting less than 9 Speciality from the picklist value,but whenever I am choosing more than 10 values in speciality .I am getting the below error:

System.QueryException: expecting a colon, found '.'
Class.DoctorsListdata1.pharmaProductList: line 86, column 1
Class.DoctorsListdata1.<init>: line 19, column 1

Thanks in advance.

Regards
Subrat
Jigar.LakhaniJigar.Lakhani
Hi Subrat,

Can you send Query which is generating dynamically?
You can get query from debug log.

Thanks & Cheers,
Jigar (pateljb90@gmail.com)
SubratSubrat
HI Jigar,

Please find the dynamic query below:
"USER_DEBUG [85]|DEBUG|Select Id,First_Name__c,Last_Name__c,Patch__c,Patch__r.Name,Speciality__c,Graduation__c,Class__c,Frequency__c,City__c, Hospitals_Name__c from Patch_Customer__c WHERE RecordType.Name = 'Doctor'  AND Speciality__c IN ('ANDROLOGIST2', 'ANESTHESIA', 'CARDILOGIST', 'CARDIOLOGY', 'CARDIOTHORASIC SURGEON', 'CHEST SPECIALIST', 'CLINICAL IMMUNOLOGY', 'COMMUNITY MEDICINE', 'CONSULTANT PHYSICIAN', 'CORNEAL SURGEON', ...)"

Regards
Subrat