Introduction
In this post we will discuss how to bypass the 5000 row limit in SuiteQL.
I’ll start with some naive approaches and then discuss the approach I settled on.
Using Rownum
The function we write will take a query as a parameter and return the results as a javascript object. We will have to wrap the query to have NetSuite return a specific 5000 rows at a time until there are no more rows.
We will be working with the following query:
1
SELECT Transaction.tranid FROM Transaction
Let’s wrap the query to return a rownum column we can work with:
1
2
3
4
SELECT *, rownum FROM (
SELECT Transaction.tranid FROM Transaction
)
Now we wrap the query to return the first 5000 rows:
1
2
3
4
5
6
7
SELECT * from (
SELECT *, rownum as r FROM (
SELECT Transaction.tranid FROM Transaction
)
) WHERE r BETWEEN 0 and 5000
You may wonder why we have to wrap the query twice. Why can’t we get away with:
1
2
3
SELECT *, rownum as r FROM (
SELECT Transaction.tranid FROM Transaction
) BETWEEN 0 and 5000
This is because 5000 row limit is applied to the outermost query.
Its as if NetSuite is adding a clause at the end of each query that says:
1
WHERE rownum <= 5000
So if we wrap the query once then the resulting query under the hood is:
1
2
3
4
SELECT *, rownum as r FROM (
SELECT Transaction.tranid FROM Transaction
) WHERE rownum BETWEEN 0 and 5000
AND rownum <= 5000
So when we want to get the next 5000 rows the query will look like:
1
2
3
4
SELECT *, rownum as r FROM (
SELECT Transaction.tranid FROM Transaction
) WHERE rownum BETWEEN 5000 and 10000
AND rownum <= 5000
This will return no rows because the second condition is always false.
Let’s write a function to get the all the results of a query:
SuiteScript Function
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
function runSuiteQLMax(theQuery){
var moreResults = true;
const results = [];
const pageSize = 5000;
const maxRows = 1000000;
var startRow = 0;
do {
var pagedQuery = `
SELECT * from (
SELECT *, rownum as r FROM (
SELECT Transaction.tranid FROM Transaction
)
) WHERE r BETWEEN ${startRow} and ${startRow + pageSize}
`
var page = query.runSuiteQL(pagedQuery).asMappedResults();
var size = page.length;
results.push(...page);
if(size < pageSize){
moreResults = false;
}
if(results.length > maxRows){
throw new Error(`Max rows exceeded: ${maxRows}`);
}
startRow += pageSize;
} while (moreResults);
return results;
}
Let’s add support for parameters passed in with an array:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
function runSuiteQLMax(argsobject){
theQuery = argsobject.query || argsobject; // we want to handle both cases runSuiteQLMax(query) and runSuiteQLMax({query: query, params: params})
params = argsobject.params || [];
var moreResults = true;
const results = [];
const pageSize = 5000;
const maxRows = 1000000;
var startRow = 0;
do {
var pagedQuery = `
SELECT * from (
SELECT *, rownum as r FROM (
${theQuery}
)
) WHERE r BETWEEN ${startRow} and ${startRow + pageSize}
`
var page = query.runSuiteQL({query: pagedQuery, params: params}).asMappedResults();
var size = page.length;
results.push(...page);
if(size < pageSize){
moreResults = false;
}
if(results.length > maxRows){
throw new Error(`Max rows exceeded: ${maxRows}`);
}
startRow += pageSize;
} while (moreResults);
return results;
}
Conclusion
The 5000 row limit is a bit of a pain, but it is possible to work around it. There still is the issue of script usage limits, so if you are in a client script that has a max usage of 1000 units, you can get max 500,000 rows before running into the limit. You can get around the limitations by using a restlet to further subdivide the query and then combine the results.
This concludes the series on SuiteQL. I’ve only scratched the surface of what is possible with SuiteQL, but you should have the tools to start writing bigger and better queries.