https://netsuiteprofessionals.com logo
Join Slack
Powered by
Is anyone able to return more than 5000 results wi...
# suiteql
m
Is anyone able to return more than 5000 results with the below function
Copy code
const results = query.runSuiteQL(options);
The help says it should be able to return at least 100,000 results
If the SuiteAnalytics Connect feature is enabled in your NetSuite account, there is no limit to the number of results this method can return. If the SuiteAnalytics Connect feature is not enabled, this method can return a maximum of 100,000 results. For more information about SuiteAnalytics Connect
b
query.ResultSet object description
m
so the result can only hold 5000 but it can query more? How could it query over 100,000 results if the result set can only hold 5000? Is that what you're also seeing in the help?
b
use the paged related methods
m
that's what I have been doing but the paged methods i've been running are far slower when there are multiple pages than searches
b
the alternative is to deal with only 5000 results at a time and modify your query to do the pagination yourself
m
okay! i'll try that but what does the max 100,000 results mean?
b
paged methods allow you to get 5000 results at a time, up to a maximum of 100000
you would not be able to get the 100001 result
m
that makes more sense. Thanks!
b
whoops, on second reading of this, i read 5000 too many times
paged methods allow you to get 1000 results at a time, up to a maximum of 100000
m
@jen I thought the same but it looks like the help is confusing and is still only 5000 as that's the most a result set can hold. The help is confusing
j
ah
For anyone interested, here is my module
Copy code
/**
 * md_query_ss2.js
 * @ssScriptName ModuleQuery
 * @ssScriptId md_query
 * @ssApiVersion 2.1
 *
 * Wrapper for query so that we can get more than 5000 results.
 *
 * last modified 2021-10-04 JB
 */

define(['N/query'], 
	function(query) {	
	
	var clump_size = 5000;
	
	function getFullResults(options) {
		
		var clump = 0;
		
		var results = [];
		
		do {
			
			var clump_start = (clump * clump_size) + 1;
			var clump_end = ((clump + 1) * clump_size);
			
			if(options.log_to_console)
				console.log('Starting Clump ' + clump + ' (' + clump_start + ' to ' + clump_end + ')');
			else if(options.log_to_scriptlog)
				log.debug({title: 'getFullResults()', details: 'Starting Clump ' + clump + ' (' + clump_start + ' to ' + clump_end + ')'});
			
			var full_sql = `
SELECT *
FROM (

	SELECT
	ROWNUM AS md_query_row_number,
	*
	
	FROM ( 
	
	` + options.sql + `
	
	)

) 

WHERE md_query_row_number BETWEEN ` + clump_start + ` AND ` + clump_end;
						
			if(options.log_to_console)
				console.log('SQL: ' + full_sql);
			else if(options.log_to_scriptlog)
				log.debug({title: 'getFullResults()', details: 'SQL: ' + full_sql});
			
			var clump_results = query.runSuiteQL({query: full_sql}).asMappedResults();
			
			for(var cr_idx = 0; cr_idx < clump_results.length; cr_idx++) { 
				
				delete clump_results[cr_idx]['md_query_row_number'];
				results.push(clump_results[cr_idx]);
				
			}
						
			if(options.log_to_console)
				console.log('Found ' + clump_results.length + ' results in this clump');
			else if(options.log_to_scriptlog)
				log.debug({title: 'getFullResults()', details: 'Found ' + clump_results.length + ' results in this clump'});
					
			clump++;
			
		} while(clump_results.length == clump_size)
						
		if(options.log_to_console)
			console.log('Returning ' + results.length + ' total results');
		else if(options.log_to_scriptlog)
			log.debug({title: 'getFullResults()', details: 'Returning ' + results.length + ' total results'});
				
		return results;
		
	}
	
	/* Return the names of the functions.  Note that these can be different from the
	 * actual names above, but for clarity let's keep them the same. 
	 */
	return {

		getFullResults: getFullResults

	}

});
so instead of doing 
query.runSuiteQL({query: sql}).asMappedResults();
I do 
md_query.getFullResults({sql: sql});
s
as a tiny aside, an easy way to ensure the function name exported at the end matches you could do 
return { getFullResults }
m
This is the most efficient i've gotten it to be and returns an array of json objects
Copy code
lib.query = (options) => {
        const sqlPageSize = options.pageSize || 5000;
        let paginatedRowBegin = 1;
        let paginatedRowEnd = sqlPageSize;
        let records = [];
        let moreRecords = true;
        do {
            let paginatedSQL = `SELECT * FROM ( SELECT ROWNUM AS ROWNUMBER, * FROM (  ${options.sql} ) )  WHERE ( ROWNUMBER BETWEEN ${paginatedRowBegin} AND ${paginatedRowEnd} )`;
            let queryResults = query.runSuiteQL( {query: paginatedSQL, params: []} ).asMappedResults();
            records.push(...queryResults );
            if ( queryResults.length < sqlPageSize ) {
                moreRecords = false;
            }
            paginatedRowBegin += sqlPageSize;
            paginatedRowEnd += sqlPageSize;
        } while ( moreRecords );

        return records;
    };
27 Views
Open in Slack
PreviousNext
Powered by