This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

Hi All -
Can someone give me a working example with `runSuiteQLPaged` please?
I want to be able to return more than 5,000 records in a restlet, but I don't find any suitable explanation on the Internet. I can't figure out how to iterate through the pages : )
Thanks!

You can find examples on GitHub:
<https://github.com/search?q=runSuiteQLPaged&type=code>

With `runSuiteQLPaged` you're limited to 100,000 results, and it might be slower than doing the pagination via SQL.

Also, I'm not sure the pagination always gives consistent results - i.e. might give duplicate records or skip records if the underlying data changes between pagination requests.

There's also the ROWNUM technique hack, but I don't recommend it
<https://timdietrich.me/blog/netsuite-suiteql-pagination/>

If you can pagination via SQL based on the the IDs would be better, e.g. `WHERE id > 1234` replacing 1234 with the last fetched ID

FWIW, to be clear even using internal ids for paging does not guarantee consistent results - NetSuite does not guarantee that record id 2 will be created after record id 1 in time.

No, but It guarantees that there are no missing ids and no duplicates, which may happen when using offset pagination techniques

It does guarantee no duplicates, but not missing ids - if you ask for a page that includes record 2 but record 1 hasn't been created, record  1 will be missed if your next requests asks for ids > 2

e.g. if you get first set of results (e.g. 1-1000), change a few of the results in the first page (e.g. 1-3) so they don't match the criteria, and then get the second page, you'll skip the results originally 1001-1003. Those records may have been created a long time ago.

my point was around the unfortunate fact that NS does not guarantee that record 1 will exist before record 2 is created.

or put another way, id values are not guaranteed to be monotonically increasing in NS.

<@U02BCAJ0BUZ> - what are the cons to using the ROWNUM approach?

Mainly performance issues. I was seeing timeouts on large/complex queries, as the query needs to fetch the whole dataset, and the potential issues above with the offset pagination

We’ve been using the ROWNUM technique without issue yet. It’s much faster than using the provided paging function

Bing Chat thinks the following: <https://stackoverflow.com/questions/241622/paging-with-oracle|When paging through results from an Oracle SQL database, it is generally better to use the >*<https://stackoverflow.com/questions/241622/paging-with-oracle|primary key>*<https://stackoverflow.com/questions/241622/paging-with-oracle| for page boundaries instead of ROWNUM 1><https://stackoverflow.com/questions/13738181/best-practice-for-pagination-in-oracle|2>.
Using ROWNUM to paginate data can be problematic because it is applied after the query is executed and the results are sorted. <https://stackoverflow.com/questions/241622/paging-with-oracle|This means that the results may not be consistent across different queries, and the performance may be slower for large datasets 1><https://sanaulla.info/2022/02/09/using-rownum-to-paginate-the-data-in-oracle/|3>.
<https://stackoverflow.com/questions/241622/paging-with-oracle|On the other hand, using the primary key for pagination ensures that the results are consistent across different queries and that the performance is faster for large datasets 1><https://stackoverflow.com/questions/13738181/best-practice-for-pagination-in-oracle|2>.
<https://stackoverflow.com/questions/241622/paging-with-oracle|Therefore, it is recommended to use the primary key for pagination when paging through results from an Oracle SQL database 1><https://stackoverflow.com/questions/13738181/best-practice-for-pagination-in-oracle|2>.