[SalesForce] SOQL QUERY WITH CreatedDate

I have two objects SetupAuditTrail(API) and RMSEventLog__c(Custom).I am trying to insert all records from SetupAuditTrail to RMSEventLog__c using a Batch class.

I want to set filter criteria in SOQL statement such that the query should insert those records from SetupAuditTrail to RMSEventLog__c whose CreatedDate is greater than CreatedDate of last record inserted in RMSEventLog__c.

I am able to capture CreatedDate of last record inserted in RMSEventLog__c.
Also I am getting my desired result by putting the value of first query in second query.

select EventOccuranceDate__c from RMSEventLog__c where Datasource__c = 
'Salesforce' AND EventCategory__c = 'SetupAuditTrail' order by CreatedDate 
desc limit 1

[![enter image description here][1]][1]

SELECT id, Action, CreatedById, CreatedDate, CreatedBy.name, Display, 
Section from SetupAuditTrail where CreatedDate > 2015-10-
01T11:51:14.000+0000

[![enter image description here][2]][2]

But my question is how to concatenate both the queries to make it single query.I was trying like this but query is not working.

SELECT id, Action, CreatedById, CreatedDate, CreatedBy.name, Display, 
Section from SetupAuditTrail where CreatedDate > select 
EventOccuranceDate__c from RMSEventLog__c where Datasource__c = 
'Salesforce' AND EventCategory__c = 'SetupAuditTrail' order by CreatedDate 
desc limit 1

Best Answer

The query you are trying will not compile in Salesforce because this type of LEFT INNER JOIN is not Supported.

What you can do is First Store the result of first query in a variable and then perform the second query.

DateTime/Date dt = [select EventOccuranceDate__c from RMSEventLog__c where Datasource__c = 'Salesforce' AND EventCategory__c = 'SetupAuditTrail' order by CreatedDate desc limit 1].EventOccuranceDate__c ;

[SELECT id, Action, CreatedById, CreatedDate, CreatedBy.name, Display, Section from SetupAuditTrail where CreatedDate >: dt]

OR simply like this:

[SELECT id, Action, CreatedById, CreatedDate, CreatedBy.name, Display, Section from SetupAuditTrail where CreatedDate >: [select EventOccuranceDate__c from RMSEventLog__c where Datasource__c = 'Salesforce' AND EventCategory__c = 'SetupAuditTrail' order by CreatedDate desc limit 1].EventOccuranceDate__c]]

Please make sure that EventOccuranceDate__c is of DateTime Type.

Related Solutions

[SalesForce] Best way to do a count() from a custom object having more than 1 million records

I think you cannot get the complete count using a SOQL, not 100% sure. Initially I thought you could use a @readOnly annotated method and then get the count but then i think that cannot be done as the you will be executing the query from the context of a VF page. But I can think there are ways of doing this, As of now I can think of 2 methods(I am sure there will be easier way of doing this):

1.) You can make a http request using the REST API. Before using this code make sure you add the salesforce url in the remote site setting. The response you get contains contains a atribute called "totalSize", which gives you the count of number of rows returned by the query (which is mentioned in the REST URL).You can run the below code in the developer console to check. to get the use the below code:

    HttpRequest req = new HttpRequest();
 req.setEndpoint('https://'+URL.getSalesforceBaseUrl().getHost()+'/services/data/v20.0/query/?q=SELECT+name+from+Account');
 req.setMethod('GET');

string autho = 'Bearer '+ userInfo.getsessionId();
 req.setHeader('Authorization', autho);

 Http http = new Http();
 HTTPResponse res = http.send(req);
 string response = res.getBody();
 string totalCount = response.substring(response.indexOf('totalSize":')+11, response.indexOf(','));
system.debug('get the total count '+ totalCount);

2.) As you mentioned you are using a VF page, you can do the rest call in the VF page itself which is easier way, using jquery:

$.ajax({
url : "https://yourinstance/services/data/v20.0/query/?q=SELECT+name+from+Account",
headers : {"Authorization": "Bearer "+ AddSessionId}, //make sure you add the sessionId
contentType : "application/json"
}).done(function(response){
    console.log('The count is '+ response.totalSize);

});

[SalesForce] SOQL Nulls Last with Orderby Picklist field doesn’t work

Sorting on picklist fields is a bit of a special case. Typically

"picklist sorting is defined by the picklist sort determined during setup." - ORDER BY Docs

You can refine this using the Reorder button on the picklist field.

However, NULLS LAST doesn't appear to be respected on picklists. See (and upvote) - Implement SOQL Order By NULLS LAST. It appears to be a long standing issue.

One possible workaround would be to create a text field that you populate with a workflow rule and field update. This would allow you to sort alphabetically and should respect null values. Note that you could get into issues with this if you multiple languages/translations (which I think is why picklists have sort orders).

Another thing to watch out for could be what are called "inactive picklist values". This is where you have assigned a value to the picklist that isn't in the current list of allowed values. These should appear after the defined values in sorting.

Best Answer

Related Solutions

[SalesForce] Best way to do a count() from a custom object having more than 1 million records

[SalesForce] SOQL Nulls Last with Orderby Picklist field doesn’t work

Related Topic