+ Start a Discussion
Muhammad Jawwad 16Muhammad Jawwad 16 

Query is not working in Start method tell me where am i wrong?

global class MonPipelineReportBatch implements Database.Batchable<sObject> {
    public String query = 'SELECT Loan_Officer_1a__c,Loan_Officer_1a__r.Email, Name, Phone, Starting_Credit_Score__c, ' 
                          +  'Status, Enrolled_On__c, Est_Re_Pull_Date__c, Realtor_Name__c ' 
                          +   ' FROM Lead';
    public EmailTemplate templateId = [Select Id,HtmlValue,Subject from EmailTemplate where name = 'LoanOfficerRecord' LIMIT 1];

    global Database.QueryLocator start(Database.BatchableContext bc) {
        Date startDate = Date.today().addMonths(-1);
        startDate = Date.newInstance(startDate.year(),startDate.month(),1);
        Date endDate = startDate;
        Integer days = Date.daysInMonth(endDate.year(), endDate.month());
        endDate = Date.newInstance(startDate.year(),startDate.month(),days);

        query += ' WHERE CreatedDate >=: ' + startDate + ' AND CreatedDate <=: ' + endDate + ' AND Loan_Officer_1a__c != null';
        return Database.getQueryLocator(query);
    }

 
Best Answer chosen by Muhammad Jawwad 16
AbhimanyuAbhimanyu
global class MonPipelineReportBatch implements Database.Batchable<sObject> {
    public String query = 'SELECT Loan_Officer_1a__c,Loan_Officer_1a__r.Email, Name, Phone, Starting_Credit_Score__c, ' 
                          +  'Status, Enrolled_On__c, Est_Re_Pull_Date__c, Realtor_Name__c ' 
                          +   ' FROM Lead';
    public EmailTemplate templateId = [Select Id,HtmlValue,Subject from EmailTemplate where name = 'LoanOfficerRecord' LIMIT 1];

    global Database.QueryLocator start(Database.BatchableContext bc) {

        query += ' WHERE CreatedDate = LAST_MONTH  AND Loan_Officer_1a__c != null';
        return Database.getQueryLocator(query);
    }
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm
 

All Answers

Raj VakatiRaj Vakati
Use the DateTime with the format option 
 
String sDateFormat = startDate.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'') ; 
String eDateFormat = endDate.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'') ;

Like below 
 
global class MonPipelineReportBatch implements Database.Batchable<sObject> {
    public String query = 'SELECT Loan_Officer_1a__c,Loan_Officer_1a__r.Email, Name, Phone, Starting_Credit_Score__c, ' 
                          +  'Status, Enrolled_On__c, Est_Re_Pull_Date__c, Realtor_Name__c ' 
                          +   ' FROM Lead';
    public EmailTemplate templateId = [Select Id,HtmlValue,Subject from EmailTemplate where name = 'LoanOfficerRecord' LIMIT 1];

    global Database.QueryLocator start(Database.BatchableContext bc) {
       
		DateTime startDate = DateTime.now();
DateTime endDate = DateTime.now();
// calicute the math here 


		String sDateFormat = startDate.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'') ; 
String eDateFormat = endDate.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'') ; 

		
        query += ' WHERE CreatedDate >=: ' + sDateFormat + ' AND CreatedDate <=: ' + eDateFormat + ' AND Loan_Officer_1a__c != null';
        return Database.getQueryLocator(query);
    }


 
Muhammad Jawwad 16Muhammad Jawwad 16
0 rows affected...Fatal Error
Fatal error
AbhimanyuAbhimanyu
Please try this,
global class MonPipelineReportBatch implements Database.Batchable<sObject> {
    public String query = 'SELECT Loan_Officer_1a__c,Loan_Officer_1a__r.Email, Name, Phone, Starting_Credit_Score__c, ' 
                          +  'Status, Enrolled_On__c, Est_Re_Pull_Date__c, Realtor_Name__c ' 
                          +   ' FROM Lead';
    public EmailTemplate templateId = [Select Id,HtmlValue,Subject from EmailTemplate where name = 'LoanOfficerRecord' LIMIT 1];

    global Database.QueryLocator start(Database.BatchableContext bc) {
       
		DateTime startDate = DateTime.now();
DateTime endDate = DateTime.now();
// calicute the math here 


		String sDateFormat = startDate.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'') ; 
String eDateFormat = endDate.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'') ; 

		
        query += ' WHERE CreatedDate >= ' + sDateFormat + ' AND CreatedDate <= ' + eDateFormat + ' AND Loan_Officer_1a__c != null';
        return Database.getQueryLocator(query);
    }
you need to remove : from when using ' in query.

Like the answer, if it worked.
 
Muhammad Jawwad 16Muhammad Jawwad 16
Batch status-completed but results are the same 0 rows fetched.
AbhimanyuAbhimanyu
Try this query in Dev Console

SELECT Loan_Officer_1a__c,Loan_Officer_1a__r.Email, Name, Phone, Starting_Credit_Score__c, Status, Enrolled_On__c, Est_Re_Pull_Date__c, Realtor_Name__c  FROM Lead WHERE Loan_Officer_1a__c != null

and then try this

SELECT Loan_Officer_1a__c,Loan_Officer_1a__r.Email, Name, Phone, Starting_Credit_Score__c, Status, Enrolled_On__c, Est_Re_Pull_Date__c, Realtor_Name__c  FROM Lead 

To me, it looks like Loan_Officer_1a__c is NULL in all leads
 
Muhammad Jawwad 16Muhammad Jawwad 16
global class MonPipelineReportBatch implements Database.Batchable<sObject> {
    public String query = 'SELECT Loan_Officer_1a__c,Loan_Officer_1a__r.Email, Name, Phone, Starting_Credit_Score__c, ' 
                          +  'Status, Enrolled_On__c, Est_Re_Pull_Date__c, Realtor_Name__c ' 
                          +   ' FROM Lead';
    public EmailTemplate templateId = [Select Id,HtmlValue,Subject from EmailTemplate where name = 'LoanOfficerRecord' LIMIT 1];

    global Database.QueryLocator start(Database.BatchableContext bc) {
        Date startDate = Date.today().addMonths(-1);
        startDate = Date.newInstance(startDate.year(),startDate.month(),1);
        Date endDate = startDate;
        Integer days = Date.daysInMonth(endDate.year(), endDate.month());
        endDate = Date.newInstance(startDate.year(),startDate.month(),days);

        query += ' WHERE CreatedDate >=: ' + startDate + ' AND CreatedDate <=: ' + endDate + ' AND Loan_Officer_1a__c != null';
        return Database.getQueryLocator(query);
    }
my query was this i'm fetching the previous month data. Look at this and correct the query please..

 
AbhimanyuAbhimanyu
global class MonPipelineReportBatch implements Database.Batchable<sObject> {
    public String query = 'SELECT Loan_Officer_1a__c,Loan_Officer_1a__r.Email, Name, Phone, Starting_Credit_Score__c, ' 
                          +  'Status, Enrolled_On__c, Est_Re_Pull_Date__c, Realtor_Name__c ' 
                          +   ' FROM Lead';
    public EmailTemplate templateId = [Select Id,HtmlValue,Subject from EmailTemplate where name = 'LoanOfficerRecord' LIMIT 1];

    global Database.QueryLocator start(Database.BatchableContext bc) {

        query += ' WHERE CreatedDate = LAST_MONTH  AND Loan_Officer_1a__c != null';
        return Database.getQueryLocator(query);
    }
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm
 
This was selected as the best answer