I am working on a MR script in which i use an SQL ...
# suitescriptv
vennila ramasamy
02/28/2023, 7:58 AMI 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?
n
Nathan L
02/28/2023, 2:14 PMTry running it in the console and seeing what it spits out.
Im not familiar with QLEditor, but i use the query module in netsuite quite frequently.
Also, if you can, post the getInputData() code here. Maybe theres just something off about how we are running the query
v
vennila ramasamy
03/02/2023, 8:39 AMhi this is the script
function 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;
}
vennila ramasamy
03/02/2023, 8:57 AMfunction 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;
}
2 Views