Using SuiteQL, what limitations are there around number of results returned, both page size and when paging through the result set

AI generated answer (using internal Slack threads):

In SuiteQL, there are limitations around the number of results returned both in terms of page size and when paging through the result set. When running a SuiteQL query, the maximum number of results returned is 5000. If you need to retrieve more than 5000 results, you can use the

runSuiteQLPaged

method which allows you to paginate the results. However, the

runSuiteQLPaged

method only takes in a

pageSize

parameter, which limits the number of results per page. For example, if you have 1,000,005 results and do a paginated search with 10 results per page, you'll get 100,000 pages of 10 and then 1 of 5. If you need to retrieve all the results, you can implement your own pagination using the

ROW_NUMBER()

function in your SuiteQL query. This allows you to specify the range of row numbers to return in your results. Here's an example of how you can implement your own pagination:

sql
SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER() 
                OVER ( 
                    ORDER BY
                        Transaction.ID
                ) LineNumber,
            Transaction.ID,    
            Transaction.TranDate,
            Transaction.Type,
            Transaction.TranID
        FROM
            Transaction
        WHERE
             ( Transaction.Voided = 'F' )
        ORDER BY
            Transaction.ID
    )
WHERE
    LineNumber BETWEEN 5000 and 10000

In this example, the query returns the results where the row number is between 5000 and 10000. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******