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 everyone,
I am hitting the /suiteql REST API endpoint with limit and offset parameters `https://.....<http://suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=2&amp;offset=100000|suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=2&amp;offset=100000>`

A loooooot:
```WHERE transaction.recordtype IN ('vendorbill','vendorcredit','vendorpayment','check','creditmemo','customerdeposit','customerrefund','deposit','depositapplication','expensereport','inventoryadjustment','inventorytransfer','invoice','itemfulfillment','itemreceipt','journalentry','customerpayment','returnauthorization','salesorder','transferorder','vendorprepayment','vendorprepaymentapplication','vendorreturnauthorization')```

The client is trying to get the transaction data to their data warehouse.

It is a long SQL query, but it basically joining `transaction` and `transactionLine` and gets the important data out of it with simple filters like `transaction.posting = 'T' AND transaction.voided = 'F'`

There are a few million journals alone, and this is only the headers. So we are trying to paginate through them, but it doesn't work.

This doesn't work also btw if you added it to the SQL itself:
```OFFSET X ROWS FETCH NEXT Y ROWS ONLY```

Does a COUNT gives you the total number of rows?

You are hitting the Netsuite hard limit of 100,000 you can't set an offset greater than that unfortunately.

```Using SuiteQL queries, you can return a maximum of 100,000 results. For more information, see query.runSuiteQLPaged(options).```

Yeah, a teammate pointed at that. The weird thing is sometimes some errors make you think the limit is 2K, not 100K, which is confusing.

I think the 2K is the maximum records you can return in a single call, the 100k is the maximum records you can get (paged) from any query. A very annoyingly limit and seems a weird limit to impose as I can't see what performance issues it would give that mean NS would impose it, especially as it knows the total count so must be querying all the records in the background.

The way I normally get round this, assuming you are calling the query from code, is to get the min and max ids from the transactions first and then loop through them splitting them in to 100K blocks (to avoid going over the 100K limit).

We are doing it this way now `.... WHERE transactionLine.uniquekey &gt; 0 ORDER BY transactionLine.uniquekey`
That's for the first query, then replace 0 with the last key you get, and repeat.