https://netsuiteprofessionals.com logo
Join Slack
Powered by
Anyone having issues with am `ORDER BY` breaking `...
# suiteql
c
Anyone having issues with am 
ORDER BY
breaking 
query.runSuiteQLPaged
?
a
Have you considered using rownum to page it manually?
c
Not yet -- it might be a defect from a recent maintenance upgrade or eFix since the same query works in one environment but not another
a
If you intend to use 
asMappedResults
paging it manually with a 
do while
loop makes more sense imho
Something like this:
Copy code
var moreRecords = true;	
		
		var paginatedRowBegin = docInfo.rowBegin;
		
		var paginatedRowEnd = docInfo.rowEnd;		
		
		var queryParams = new Array();

		var records = new Array();

		do {			
	
			var paginatedSQL = 'SELECT * FROM ( SELECT ROWNUM AS ROWNUMBER, * FROM (' + docInfo.query + ' ) ) WHERE ( ROWNUMBER BETWEEN ' + paginatedRowBegin + ' AND ' + paginatedRowEnd + ')';
		
			var queryResults = query.runSuiteQL( { query: paginatedSQL, params: queryParams } ).asMappedResults(); 	
				
			records = records.concat( queryResults );	
					
			if ( queryResults.length < 5000 ) { moreRecords = false; }
		
			paginatedRowBegin = paginatedRowBegin + 5000;
				
		} while ( moreRecords );
I took this from @tdietrich's sql tool.
c
Thanks for the input - I'll take a look at it! I remember having some inconsistent results when it comes to pairing ROWNUM with ORDER BY - is there anything to look out for there?
a
I've used this function without a problem.
c
Thank you
This turns out to be related to some unfortunate sub-optimal architecture in 
runSuiteQLPaged()
. Again, our query works swiftly as written in 
runSuiteQL()
, but with the paged method, it breaks. It turns out it's breaking because it's timing out; even though the resultset contains 67 rows, so less than a page, something about the way it's processing the ORDER BY across the primary table in our query causes the paged method to grind to a halt. We fixed it by taking a cue from the function above and running our ORDER BY on a subquery so it's not trying to manage the full table. Thanks for the direction! 🙌
a
Interesting. Would've thought the query optimizer would've taken care of that.
t
Hey guys, a couple of comments about things mentioned in this thread... The issue with ROWNUM, and the reason you have to be careful with how you use it for pagination, is that its value gets calculated after filters have been applied to a query. I talk a little about that here - https://timdietrich.me/blog/netsuite-suiteql-pagination/ That's why, in the code snippet that @Alan Fitch shared above, I have the various nested queries. Regarding the sometimes odd performance that we see - especially in queries that you'd think are able to use indexes - there seems to be a level of abstraction that SuiteQL introduces, and as a result, we're not hitting the underlying database directly. I suspect that it has to do with how permissions are enforced, and other things as well. In my experience, that level of abstraction is different when you're running queries via SuiteTalk REST. That's one of the reasons why I developed the SuiteQL Query API ( https://timdietrich.me/blog/netsuite-suiteql-query-api/ ). Under the hood, it still uses SuiteQL - but because of how it's using it, performance is often better, the results seem more in line with what you'd expect, and errors are often more sensible. In any case, @Clay Roper, I'm glad you were able to find a solution. SuiteQL is incredibly powerful. But it sure is bizarre and frustrating sometimes.
a
While your here, quick question. Why is it that under the tables reference, I can't find 
TransactionShipment
but on the joins for transactions it lists it?
I've used it and it worked but I'm just wondering about the underlying call to 
recordscatalog/rcendpoint.nl?action=\getRecordTypes&data
and why it's funky this way.
t
I'm not sure why the records catalog's API works that way, but it is frustrating, because there are a number of secondary tables that are buried as a result. That's one of the reasons that I've been working on this: https://timdietrich.me/blog/netsuite-suiteql-schema-tool-preview/ It exposes all of the tables, regardless of how they're presented in the API. It also lets you search across all tables for a column, which can be very helpful at times. I've had to pause that project - and pretty much my entire life. I injured my back, and as a result, for the past 4 weeks I've spent 99% of the time on my back. Writing code and giving presentations, while flat on your back, sucks. I'd give just about anything to be able to sit or stand at a desk like a normal person.
4 Views
Open in Slack
PreviousNext
Powered by