CODEWITHSUNDEEP
sqldatabasesalesforce
Nolan Pestano
Nolan Pestano

Reputation: 177

Select rows from SQL, ignoring limit when multiple

I am trying to grab some data from my table, but I am concerned that it could be broken by multiple parts. What I mean is that I have a selector for a Collection Account with a LIMIT 100 clause at the end of my selection, for run time purposes. However, there is a chance that, because of that limit, it won't grab all of the respective information, meaning next time I run the process, it would grab all the others and now I have two extracts pointing to one account.

Here is what I have written.

SELECT blah
FROM Events 
WHERE Collection_Account__r.Status__c = 'Closed'
LIMIT 100

So, how could I ignore the limit when I see that there are multiple Events regarding to one Collection_Account

Upvotes: 0

Views: 276

Answers (1)

eyescream
eyescream

Reputation: 19637

Your question is bit weird, has few disconnected / misguided parts; what exactly do you need to achieve?

"LIMIT" (proper way)

If you want to work with chunks of max 100 records (because you're concerned with timeouts or memory usage or whatever) - Salesforce has ability to fetch next chunk of data. Like cursor statement in normal database. Assuming you're using REST API for your query - look into nextRecordsUrl. DO NOT put LIMIT into your query if you want to use this because LIMIT will "win"

So once you have it the "only" thing would be to reduce the default chunk size from 2000 records to 100. You can do it by sending a Sforce-Query-Options: batchSize=100 HTTP header with your query.

LIMIT (poor way)

If these sound like too much work and you think the amount of data will be small you can make handmade cursor using LIMIT and OFFSET. It won't let you go more than 2000 rows but might be just enough and simple to understand. Your first query would be LIMIT 100, then LIMIT 100 OFFSET 100, then LIMIT 100 OFFSET 200...

now I have two extracts pointing to one account

I don't think I understand that one but if you need "most recent event for all these accounts" or "Accounts and all their events created after $lastSyncDate" - the natural thing would be to rewrite the query.

select id,
    (select id
    from events
    order by lastmodifieddate desc
    limit 1)
from account
where Status__c = 'Closed'
limit 100

experiment with this and then add WHERE CreatedDate > 2022-09-01T00:00:00Z to the from events and see how it works?

Upvotes: 1

Related Questions