You need to sign in to do that
Don't have an account?
Subrat
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
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
Can you send Query which is generating dynamically?
You can get query from debug log.
Thanks & Cheers,
Jigar (pateljb90@gmail.com)
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