I am working on a MR script in which i use an SQL ...
# suitescriptv
vennila ramasamy
03/02/2023, 1:15 PMI am working on a MR script in which i use an SQL query in a getInputData() function. it returns 0 rows as the output. but when i run the query on the QLEditor it returns more rows. I have no clue how to find the issue? Can anyone please help me? Below is my getInputData function,
function getInputData() {
log.debug('Getting input');
var sql = ` SELECT event.id
FROM calendarevent event
WHERE event.custevent_true_time_entry is not null and event.custevent_true_time_entry NOT IN (SELECT time.id FROM timebill time)
`;
var queryParams = new Array();
var rows = selectAllRows( sql, queryParams );
log.audit( { title: 'getInputData - number of rows selected', details: rows.length } );
return rows;
}
e
eblackey
03/02/2023, 1:18 PMWhat is “selectAllRows()” ? Is that another method id your MR script?
v
vennila ramasamy
03/02/2023, 1:40 PMfunction selectAllRows(sql, queryParams = new Array()){
try {
log.debug('entering selectAllRows');
var moreRows = true;
var rows = new Array();
var paginatedRowBegin = 1;
var paginatedRowEnd = 5000;
do {
log.debug('entering do');
var paginatedSQL = 'SELECT * FROM ( SELECT ROWNUM AS ROWNUMBER, * FROM (' + sql + ' ) ) WHERE ( ROWNUMBER BETWEEN ' + paginatedRowBegin + ' AND ' + paginatedRowEnd + ')';
var queryResults = query.runSuiteQL( { query: paginatedSQL, params: queryParams } ).asMappedResults();
rows = rows.concat( queryResults );
if ( queryResults.length < 5000 ) { moreRows = false; }
paginatedRowBegin = paginatedRowBegin + 5000;
} while ( moreRows )
} catch( e ) {
log.error( { title: 'selectAllRows - error', details: { 'sql': sql, 'queryParams': queryParams, 'error': e } } );
}
return rows;
}
e
eblackey
03/02/2023, 1:47 PMI think I would add some logging in your selectAllRows method to see if you’re getting results after the runSuiteQL call. I suspect the issue is in that method somewhere. I would also consider moving the runSuiteQL call to the getInputMethod and bypassing your selectAllRows method temporarily to make sure the query is returning results.
v
vennila ramasamy
03/02/2023, 1:50 PMok, please let me know
s
scottvonduhn
03/02/2023, 2:21 PMyou can have the getInputData function return a SuiteQL query directly, and NetSuite will run it for you, passing the results to the map or reduce phase (whichever is next), with essentially no row limit, though the result set has a memory limit of 200 MB
💯 3
v
vennila ramasamy
03/02/2023, 2:27 PMhow can i do it?
s
scottvonduhn
03/02/2023, 2:29 PM Copy code
function getInputData() {
const sql = `SELECT event.id
FROM calendarevent event
WHERE event.custevent_true_time_entry is not null and event.custevent_true_time_entry NOT IN (SELECT time.id FROM timebill time)
`;
return { type: 'suiteql', query: sql };
}👍 1
scottvonduhn
03/02/2023, 2:30 PMat the end of getInputData, just return an object with type set to ‘suiteql’ and the query set to your SQL string. You can also add an optional params array if you have any.
scottvonduhn
03/02/2023, 2:32 PMscottvonduhn
03/02/2023, 2:33 PMrefer to the section: Use a SuiteQL Query in a Map/Reduce Script
c
Charan
03/02/2023, 5:51 PM@scottvonduhn Thanks for the explanation. I have a question - Is it mandatory to use the pagination function to get more than 1000 results in getInputStage when using SuiteQL ?
s
scottvonduhn
03/02/2023, 5:53 PMIt's not only not mandatory, you can't use pagination at all when having netsuite execute the query returned by getInputData. It happens outside the context of the script
👍 1
c
Charan
03/02/2023, 5:58 PMYeah that’s correct. I just reviewed the function. Thanks! I’m very curious to know, If SQL query returns more than 5000 results then getInputStage can process It?
e
eblackey
03/02/2023, 6:38 PM@scottvonduhn I appreciate you sharing this. I didn’t know about this feature. Do you happen to know what happens if a query returns more than 200MB of data? Does it just return everything it can up to that amount or is an exception thrown?
s
scottvonduhn
03/02/2023, 7:27 PMYes, and that is true for both saved searches AND SuiteQL queries, if you return a query or search from getInputData, you essentially have no row limit. Exceeding the 200MB serialized result data will throw an error that stops the M/R script, however it’s not very likely. And, I have found that M/R scripts perform poorly when processing more than 40-50 thousand results anyway, so I usually limit the script to some lower amount anyway, and just have it run repeatedly until all results are handled.
❤️ 1
v
vennila ramasamy
03/03/2023, 7:50 AMThank you it works
12 Views