How to get the records for null value in the picklist field using dynamic soql

Question

I have a picklist field Executed__c with None,Yes,No values in it.The query which i have written is fetching the records when the user selects yes or No.But when user selects None,null value is getting stored in the variable.But those records are not fetched Even though there are records for null value.How can i get those records?Please guide me.

Below is my apex class for that

    public class PrivateEquityReport
    {
      public Boolean isXLS {get;set;} 
      public List<Fund_Equity__c> activityRecords;
      public String displayPrivateEquity = '';

      ApexPages.StandardSetController regionStandardSetController{get;set;}

      public PrivateEquityReport()
     {
         activityRecords= new list<Fund_Equity__c>();
         regionStandardSetController=new ApexPages.StandardSetController(new  list<Fund_Equity__c>());
         String reportType =          System.currentPageReference().getParameters().get('reportType');
         if(reportType=='XLS')
          isXLS = true;
         else
          isXLS = false;    

      }   

  String baseQuery  = 'SELECT '+''+'Id,Due_Date__c,Fund__r.Name,Company__r.Name,Subject__c,Comments__c,Date_Received__c,Business_Review__c,Attorney_Review__c '+''+'FROM Fund_Equity__c WHERE Team__c=\'Equity\' AND RecordType.Name=\'Amendment\'';

  public String getReportText()
 { 

     system.debug('*************'+isXLS);

     String checkQuery = baseQuery;

     String executedCheck=System.currentPageReference().getParameters().get('Executed');
     System.debug('*******executed check******'+executedCheck);
     String reportFromDateVar= System.currentPageReference().getParameters().get('dFromDate');
     Date fromDateVar = null;
     if(reportFromDateVar != null)
     fromDateVar = Date.valueOf(reportFromDateVar);
      System.debug('*****From Date***'+fromDateVar );

     String reportToDateVar  =System.currentPageReference().getParameters().get('dToDate');
     Date toDateVar = null;
     if(reportToDateVar != null)
     toDateVar = Date.valueOf(reportToDateVar );
     System.debug('*****To Date***'+toDateVar );

     String reportReceiveFromDateVar= System.currentPageReference().getParameters().get('rFromDate');
     Date receivefromDateVar = null;
     if( reportReceiveFromDateVar != null)
     receivefromDateVar = Date.valueOf( reportReceiveFromDateVar);
     System.debug('*****Receive From Date***'+receivefromDateVar );

     String  reportReceiveToDateVar = System.currentPageReference().getParameters().get('rToDate');
      Date receivetoDateVar = null;
      if( reportReceiveToDateVar != null)
      receivetoDateVar = Date.valueOf( reportReceiveToDateVar );
      System.debug('****Receive To Date***'+receivetoDateVar );


      if((fromDateVar!= null)&&(toDateVar!=null))
     {
      checkQuery =checkQuery  +' '+'and (Due_Date__c>=:fromDateVar AND Due_Date__c<= :toDateVar )';
      system.debug('************3'+checkQuery  );
     }

     if((receivefromDateVar != null)&&(receivetoDateVar !=null))
     {
      checkQuery  =checkQuery+' '+'and (Date_Received__c>=:receivefromDateVar AND Date_Received__c<= :receivetoDateVar )';
      system.debug('************3'+checkQuery  );
     }

    checkQuery=checkQuery +' '+' and Executed__c=:executedCheck';
    system.debug('************3'+checkQuery);
     regionStandardSetController=new ApexPages.StandardSetController(Database.getQueryLocator(checkQuery));

     activityRecords= regionStandardSetController.getRecords();
     system.debug(activityRecords);
     system.debug(activityRecords.size());
   // System.debug('*****records*****'+activityRecords);

     if(isXLS)
    {
     displayPrivateEquity ='<table><tr><td>&nbsp;</td></tr><tr><td>&nbsp;</td></tr></table>' + displayPrivateEquity ;                
     displayPrivateEquity =    prepareReportSinglePagePrivateEquity(activityRecords,displayPrivateEquity );
    }
    else
    {

     displayPrivateEquity  = prepareReportSinglePagePrivateEquity(activityRecords,displayPrivateEquity );
     displayPrivateEquity +='<DIV style="page-break-after:always"></div>';  
    }     
      return  displayPrivateEquity;
}

  Public String prepareReportSinglePagePrivateEquity(List<Fund_Equity__c>     fundListVar, String reportString)
 {


   reportString+='<table width="100%" table-layout:"fixed" style="font-family: sans-serif;font-size: 12px" border="1" cellspacing="0" cellpadding="2">';
   reportString+='<THEAD>';
   reportString+='<tr align="left" style="background-color:#C0C0C0; color: ;"><th width="5%">'+'Due Date</th>'+'<th>'+'Fund</th>'
          +'<th width="5%">'+'Company</th>'+'<th width="5%">'+'Subject</th>'
          +'<th>'+'Comments</th>'+'<th>'+'Date Received</th>'
          +'<th>'+'Business Review </th>'+'<th width="5%">'+'Attorney Review</th></tr>';

   reportString+='</THEAD>';       

 }    

}

I am taking params value from another controller as below

 public PageReference viewReport() 
{

    String params = '';
    if(dueFrom.Due_Date__c!= null)
    params+= '&dFromDate='+String.valueOf(dueFrom.Due_Date__c);

   if(dueTo.Due_Date__c!= null)
    params+= '&dToDate='+String.valueOf(dueTo.Due_Date__c);

   if(receivedFrom.Date_Received__c!= null)
    params+= '&rFromDate='+String.valueOf(receivedFrom.Date_Received__c);

   if(receivedTo.Date_Received__c!= null)
    params+= '&rToDate='+String.valueOf(receivedTo.Date_Received__c);

   params+= '&reportType='+reportType;

   params+= '&Executed='+fundActivityObj.Executed__c;
   System.debug('**********Executed******'+params);


   if(params.startsWith('&'))
   params = params.substring(1);
   System.debug('*****Final*****'+params);

   PageReference pageRef = null;    
   if(reportType=='PDF')
        pageRef = new PageReference('/apex/EquityTrackingPDF?'+params);
    else if(reportType=='XLS')
        pageRef = new PageReference('/apex/EquityTrackingXLS?'+params);
  pageRef.setRedirect(false);  

return pageRef;

}

VF Page

<apex:page sidebar="true" showHeader="true"  controller="PrivateEquityCriteria" tabStyle="Fund_Activity__c">

<apex:sectionHeader title=" Tracking report"     subtitle="Criteria"/>
 <apex:panelGrid id="test">
    <apex:pageMessages ></apex:pageMessages> 
 </apex:panelGrid> 
   <apex:form > 
      <apex:pageBlock title="Funds">
      <apex:pageBlockSection title="AI Private Equity Weekly Legal Tracking" columns="2" >
      <apex:inputField label="Executed" value=" {!fundActivityObj.Executed__c}"/>
      <apex:inputCheckbox label="Due Date" value="{!dueCheckBox}" selected="true"/> 

     <apex:inputField label="Due Date From" value="{!dueFrom.Due_Date__c}"/>
     <apex:inputField label="Due Date To" value="{!dueTo.Due_Date__c}"/>
     <apex:inputField label="Due Received From" value="{!receivedFrom.Date_Received__c}"/>
     <apex:inputField label="Due Received To" value="{!receivedTo.Date_Received__c}"/>

    <apex:selectList id="reportTypeId" value="{!reportType}" size="1" label="Report Type" > 
   <apex:selectOption itemValue="PDF" itemLabel="PDF"/>
   <apex:selectOption itemValue="XLS" itemLabel="XLS"/>
  </apex:selectList>
 </apex:pageBlockSection>

   <apex:pageBlockButtons location="bottom">
    <apex:commandButton value="Run Report" action="{!viewReport}"/> 
   </apex:pageBlockButtons>

 </apex:pageBlock>

Thanks.

Answer 1

In your viewReport() method where you're doing

params+= '&Executed='+fundActivityObj.Executed__c;

null gets converted to 'null' you can check this in sample

String var = null; 
String var2 = ''+null; 
System.assertEquals(null, var); 
System.assertEquals('null', var2);

So, 1. If you want null then change your code in viewReport() method as

if(null != fundActivityObj.Executed__c)
   params+= '&Executed='+fundActivityObj.Executed__c;

So you don't have a request param itself and you get null in getReportText().

or else

2. Step 1: change your code in viewReport()

   params+= '&Executed='+(null == fundActivityObj.Executed__c)?'':fundActivityObj.Executed__c;

And Step 2: Here in

String executedCheck=System.currentPageReference().getParameters().get('Executed');

you will get '' as value for executedCheck and it is not null.

So modify in getReportText()

executedCheck = (null == executedCheck || '' == executedCheck )?null:executedCheck ;