Published on December 12, 2021.

This weekend I worked on a NetSuite project that involved pulling very large amounts of data from NetSuite based on a user's search criteria. And of course, I'm using SuiteQL.

What made this project a little challenging is that in addition to displaying the data in what can best be thought of as pages, I also needed to show the total of number rows that met the user's search criteria. In other words, I needed to show users something like this: "2,500 records were found that meet your criteria. Displaying records 26 through 50."

I've needed to do this sort of thing in the past - for example, in the SuiteQL Query Tool. However, the technique that I've previously used required that the query be run twice - once with pagination filters to get the total number of records that meet the criteria, and a second time to get the actual data. That technique works, but it can be terribly inefficient.

It occured to me that with SuiteQL's support of the WITH clause (which I discussed in this post), I could use common table expressions (CTEs) to not only simplify the query, but to also make it much more efficient.

The Query

I think the easiest way to demonstrate this technique is with a sample query.

WITH 

	EmployeeData AS
		(
			SELECT
				ID,
				FirstName,
				LastName,
				Email
			FROM
				Employee
			WHERE
				( Email LIKE '%@netsuite.com' )
				AND ( LastName IS NOT NULL )
				AND ( FirstName IS NOT NULL )
		),

	EmployeeSummary AS
		( SELECT COUNT(*) AS RecordCount FROM EmployeeData )

SELECT
	*
FROM
	(
	
		SELECT
			ROWNUM - 1 AS RN,
			*
		FROM
			(

				SELECT
					* 
				FROM
					(

						SELECT
							RecordCount,
							NULL AS ID,
							NULL AS LastName,
							NULL AS FirstName,
							NULL AS Email
						FROM
							EmployeeSummary

						UNION ALL

						SELECT
							0,
							EmployeeData.ID,
							EmployeeData.LastName,
							EmployeeData.FirstName,
							EmployeeData.Email
						FROM
							EmployeeData
		
					)
				ORDER BY
					RecordCount DESC,
					UPPER( LastName ),
					UPPER( FirstName )

			)
			
	)
WHERE
	( RN = 0 ) OR ( RN BETWEEN 1 AND 10 )

The query is available in the SuiteQL Query Tool's "Remote Library." Just search for the keyword "pagination."

The WITH Clause and CTEs

In the WITH clause I'm defining two CTEs. The first CTE, which I'm calling EmployeeData, is the query that I really want the results from. The only thing missing from it are the pagination criteria. So that query essentially returns all rows that meet my criteria.

The second CTE, which I'm calling EmployeeSummary, simply gets the total number of records returned by the first CTE. It's surprising to me that you can have one CTE refer to a previously defined CTE. In any case, it works.

So we have two CTEs. One has the actual data the needs to be returned, and the other has a single row with a single column whose value is the total number of rows in the first CTE.

To return the data from both CTEs, I'm doing a UNION ALL on the two CTEs, and massaging their columns so that the data is consistent. Notice that in the first SELECT statement of the UNION ALL, I'm specifying the RecordCount value from the EmployeeSummary CTE, followed by NULL values for each of the second CTE's columns. In the second SELECT statement of the UNION ALL, I'm indicating that I want zero returned for the RecordCount column, and then the actual column values from the second CTE. Again, this ensures that the two SELECT statements return columns in equivalent data types.

Adding Pagination Support

Now the tricky part - handling the pagination, and returning the total record count at the same time.

To pull that off, I'm wrapping the UNION ALL subquery in three additional SELECT statements. The inner most of those three statements returns all of the data from the UNION ALL, and sorts them by the RecordCount value in descending order, followed by the last name and first name of the employees. The reason for sorting them initially by the RecordCount in descending order is that it results in the row with the RecordCount being the first row returned by the query. When I get the query results back, I know that the first row contains the total record count. I can grab its value, and then pop that row from the recordset.

Moving out to the next SELECT statement, I'm returning the results of the query and adding a ROWNUM column value to the response. I'm subtracting 1 from the ROWNUM value, so that the first row has a value of 0. This essentially assigns row numbers as an index to the response, and does so using a zero-based index. So the first row is assigned a ROWNUM value (which I'm aliasing as column RN) of 0, followed by an RN of 1, and so on.

And finally, in the outermost SELECT statement, I'm indicating the specific rows that I want returned based on the pagination. Again, I always want the first row (which contains the record count) returned. I'm also indicating the additional data rows that I want returned - for example, the rows where "RN BETWEEN 1 AND 10" - which is how the pagination is implemented.

The Result

Here's what the result looks like.

Click the image to view a larger version.

If I wanted the next "page" of results, I would simply run the query with this WHERE clause: ( RN = 0 ) OR ( RN BETWEEN 11 AND 20 )

That would return the "record count" row, followed by the next 10 rows of actual data.

SuiteQL's Support for TOP, FETCH, and OFFSET

If you're wondering what makes pagination with SuiteQL so challenging, it's because SuiteQL's support for the SQL clauses that you might normally use for pagination (such as FETCH and OFFSET) are limited. Recently, NetSuite added support for both the TOP and FETCH clauses, which is helpful. However, there is still no support for the OFFSET clause.

For example, take this query.

SELECT
	ID,
	LastName,
	FirstName,
	Phone,
	Email
FROM
	Employee
WHERE
	Email LIKE '%@netsuite.com'
ORDER BY
	LastName,
	FirstName
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY

The result is a recordset consisting of the first 10 rows. The OFFSET clause is simply ignored, and no error is thrown to indicate that the clause isn't supported.

Hopefully we'll see support for OFFSET at some point. Until then, we'll need to continue using techniques based on ROWNUM.

If you'd like some background on ROWNUM-based pagination techniques, take a look at this post: Paginating SuiteQL Query Results

Wrapping Up

The technique that I shared in this post has changed how I handle pagination in the apps that I'm developing. I hope you find it to be helpful.