2

I'm using CLIq and scheduled a task that is supposed to run every x-hours. My issue is that my SOQL query gets all the items from the database where I only need to extract the stuff that got updated in the last x-hours. How can I limit my query to that ?

This is my process-config.xml file.

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
    <bean id="AMP_AIMS" class="com.salesforce.dataloader.process.ProcessRunner" singleton="false">
        <description>Created by Dataloader Cliq.</description>
        <property name="name" value="AMP_AIMS"/>
        <property name="configOverrideMap">


            <map>
                <entry key="dataAccess.name" value="...\AMP_AIMS\write\AMP_AIMS.csv"/>
                <entry key="dataAccess.readUTF8" value="true"/>
                <entry key="dataAccess.type" value="csvWrite"/>
                <entry key="dataAccess.writeUTF8" value="true"/>
                <entry key="process.enableExtractStatusOutput" value="true"/>
                <entry key="process.enableLastRunOutput" value="true"/>
                <entry key="process.lastRunOutputDirectory" value="...\AMP_AIMS\log"/>
                <entry key="process.operation" value="extract"/>
                <entry key="process.statusOutputDirectory" value="...\AMP_AIMS\log"/>
                <entry key="sfdc.bulkApiCheckStatusInterval" value="5000"/>
                <entry key="sfdc.bulkApiSerialMode" value="5000"/>
                <entry key="sfdc.debugMessages" value="false"/>
                <entry key="sfdc.enableRetries" value="true"/>
                <entry key="sfdc.endpoint" value="https://test.salesforce.com/services/Soap/u/24.0"/>
                <entry key="sfdc.entity" value="Agency_Profile__c"/>
                <entry key="sfdc.extractionRequestSize" value="500"/>
                <entry key="sfdc.extractionSOQL" value="Select a.Total_Annual_Sales__c, a.Market_Specialties__c, a.Market_Focus__c,  a.Destination_Specialties__c, a.CreatedDate, a.Agency_Website__c, a.Agency_Business_Email__c From Agency_Profile__c a"/>
                <entry key="sfdc.insertNulls" value="false"/>
                <entry key="sfdc.loadBatchSize" value="100"/>
                <entry key="sfdc.maxRetries" value="3"/>
                <entry key="sfdc.minRetrySleepSecs" value="2"/>
                <entry key="sfdc.noCompression" value="false"/>
                <entry key="sfdc.password" value="blabla"/>
                <entry key="sfdc.proxyHost" value=""/>
                <entry key="sfdc.proxyNtlmDomain" value=""/>
                <entry key="sfdc.proxyPassword" value=""/>
                <entry key="sfdc.proxyPort" value=""/>
                <entry key="sfdc.proxyUsername" value=""/>
                <entry key="sfdc.timeoutSecs" value="60"/>
                <entry key="sfdc.useBulkApi" value="false"/>
                <entry key="sfdc.username" value="bla"/>
            </map>
        </property>
    </bean>
</beans>
raym0nd
  • 3,172
  • 7
  • 36
  • 73
  • 1
    I'm not sure how to express the "last X hours", but if possible it would likely by using the lastmodifieddate field and date methods in a where clause on your soql statement. http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_date_functions.htm – Samuel DR Oct 30 '12 at 07:40
  • This doc describes the getUpdated() method that is available in the API. However I'm not sure at the moment if you can access this via the dataloader http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_getupdated.htm I think Talend supports the getUpdated() api method but I'm not sure that the APEX dataloader does as I can't find a reference to it in the docs. – Born2BeMild Oct 30 '12 at 10:15
  • 1
    @Sdry: I was fine with sth similar to this in the past `SELECT Id, Name FROM Account where LastModifiedDate = TODAY AND HOUR_IN_DAY(LastModifiedDate) > 9`. You'd shedule it to run at 12, then similar one at 3 PM... Still not ideal because in case of failure of one of runs you'd want to catch up... so some scripting around the loader to save time of last succesful run is neccessary. – eyescream Oct 30 '12 at 11:20
  • @eyescream This worked for me thanks. Can you rewrite your comment in a response so I can give you some karma. – raym0nd Oct 30 '12 at 13:17
  • Hi, I need to do something similar to what's described here however I need my query to run every hour. I see issues occuring when the date changes over after midnight. LastModifiedDate = TODAY will not work if the modification has occured prior to TODAY and since the last job executed. For example if I execute my job at half past every hour. Does anyone know of a way to cover this scenario also? Thanks. – SalesforceQueries Dec 01 '15 at 15:50

1 Answers1

1

I was fine with sth similar to this in the past:

SELECT Total_Annual_Sales__c, Market_Specialties__c, Market_Focus__c,  Destination_Specialties__c, CreatedDate, Agency_Website__c, Agency_Business_Email__c
FROM Agency_Profile__c
WHERE LastModifiedDate = TODAY AND HOUR_IN_DAY(LastModifiedDate) > 9

You'd shedule it to run at 12, then similar one at 3 PM...

Still not ideal because in case of failure of one of runs you'd want to catch up... so some scripting around the loader to save time of last succesful run is neccessary for a failsafe solution.

eyescream
  • 18,088
  • 2
  • 34
  • 46
  • hi, I run the sql as Select a.Id From Account a Where a.LastModifiedDate = TODAY AND HOUR_IN_DAY(a.LastModifiedDate) > 1 . I want to query within last one hour. but no rows appear even though there is some data I manually updated. – kitokid Jul 18 '13 at 07:54
  • @kitokid `HOUR_IN_DAY(LastModifiedDate) > 1` will return you stuff updated between 1 and 2 AM. I think your best option is to use bind variable with `:System.now().addHours(-1)` or if it has to be pure SOQL - maybe just sort them by last mod date descending and check in loop? – eyescream Jul 19 '13 at 06:25