Examples of Using SuiteQL in the N/query Module
The following examples demonstrate how to create and run SuiteQL queries using the features in the N/query module.
Convert an Existing Query to SuiteQL
This example RESTlet loads a SuiteAnalytics Workbook query with an ID of customworkbook237 and runs it. The RESTlet then converts the query to SuiteQL and runs it. The RESTlet returns both sets of query results.
/**
* @NApiVersion 2.x
* @NScriptType restlet
*/
define(['N/query'], function(query) {
return {
get: function(context) {
// Load the workbook by name (record ID)
var openSalesOrders = query.load('customworkbook237');
// Run the query
var resultQuery = openSalesOrders.run();
// Convert the query to its SuiteQL representation
var openSalesOrdersQL = openSalesOrders.toSuiteQL();
// Examine the SuiteQL query string
var suiteQL = openSalesOrdersQL.query;
// Run the SuiteQL query
var resultSuiteQL = query.runSuiteQL(suiteQL);
// Compose the RESTlet response
var response = {
query: openSalesOrders,
resultQuery: resultQuery,
suiteQL: suiteQL,
resultSuiteQL: resultSuiteQL
};
// Return the response
return JSON.stringify(response);
}
}
});
Create a Custom SuiteQL Query
This example RESTlet constructs a custom query string using SuiteQL, runs the query, and returns the query results.
/**
* @NApiVersion 2.x
* @NScriptType restlet
*/
define(['N/query'], function(query) {
return {
get: function(context) {
// Construct the SuiteQL query string
var suiteQL =
"SELECT "+
" \"TRANSACTION\".tranid AS tranidRAW /*{tranid#RAW}*/, "+
" \"TRANSACTION\".trandate AS trandateRAW /*{trandate#RAW}*/, "+
" \"TRANSACTION\".postingperiod AS postingperiodDISPLAY /*{postingperiod#DISPLAY}*/, "+
" BUILTIN.DF(\"TRANSACTION\".postingperiod) AS postingperiodDISPLAY /*{postingperiod#DISPLAY}*/, "+
" BUILTIN.DF(\"TRANSACTION\".status) AS statusDISPLAY /*{status#DISPLAY}*/, "+
" BUILTIN.DF(transactionLine.item) AS transactionlinesitemDISPLAY /*{transactionlines.item#DISPLAY}*/, "+
" BUILTIN.DF(\"TRANSACTION\".entity) AS entityDISPLAY /*{entity#DISPLAY}*/, "+
" transactionLine.quantity * -1 AS transactionlinesquantity /*- {transactionlines.quantity}*/, "+
" BUILTIN.CONSOLIDATE(transactionLine.netamount, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 396, 'DEFAULT') AS transactionlinesnetamountCU /*{transactionlines.netamount#CURRENCY_CONSOLIDATED}*/, "+
" BUILTIN.CURRENCY(BUILTIN.CONSOLIDATE(transactionLine.netamount, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 396, 'DEFAULT')) AS transactionlinesnetamountCU_C /*{transactionlines.netamount#CURRENCY_CONSOLIDATED}*/, "+
" CUSTOMRECORD41.custrecord14 AS custrecord15customrecord41c /*{custrecord15<customrecord41.custrecord14#RAW}*/ "+
"FROM "+
" \"TRANSACTION\", "+
" CUSTOMRECORD41, "+
" \"ACCOUNT\", "+
" TransactionAccountingLine, "+
" transactionLine "+
"WHERE "+
" ((((\"TRANSACTION\".\"ID\" = CUSTOMRECORD41.custrecord15 AND TransactionAccountingLine.\"ACCOUNT\" = \"ACCOUNT\".\"ID\"(+)) AND (transactionLine.\"TRANSACTION\" = TransactionAccountingLine.\"TRANSACTION\" AND transactionLine.\"ID\" = TransactionAccountingLine.transactionline)) AND \"TRANSACTION\".\"ID\" = transactionLine.\"TRANSACTION\")) "+
" AND ((UPPER(\"TRANSACTION\".\"TYPE\") IN ('SALESORD') AND UPPER(\"TRANSACTION\".status) IN ('SALESORD:D', 'SALESORD:E', 'SALESORD:B') AND UPPER(\"ACCOUNT\".accttype) IN ('INCOME') AND (NOT( "+
" transactionLine.itemtype IN ('ShipItem') "+
" ) OR transactionLine.itemtype IS NULL) AND ((transactionLine.quantity * -1) - NVL(transactionLine.quantitycommitted, 0)) - NVL(transactionLine.quantityshiprecv, 0) > 0 AND NVL(transactionLine.mainline, 'F') = 'F' AND NVL(transactionLine.taxline, 'F') = 'F' AND NVL(transactionLine.isclosed, 'F') = 'F')) "+
"ORDER BY "+
" \"TRANSACTION\".trandate ASC NULLS LAST";
// Run the SuiteQL query
var resultSuiteQL = query.runSuiteQL(suiteQL);
// Compose the RESTlet response
var response = {
resultSuiteQL: resultSuiteQL
};
// Return the response
return JSON.stringify(response);
}
}
});
Accept a SuiteQL Query as an Argument
This example RESTlet accepts a SuiteQL query as a provided argument to the get endpoint, runs the query, and returns the query results.
/**
* @NApiVersion 2.x
* @NScriptType restlet
*/
define(['N/query'], function(query) {
return {
get: function(context) {
return runQuery(query, context);
},
post: function(context) {
return runQuery(query, context);
}
}
function runQuery(query, context) {
// Get the SuiteQL query string from the arguments. For example, the
// SuiteQL query may look like the following:
//
// &suiteQL=select%20type%2C%20BUILTIN.DF(type)%2C%20tranid%2C%20trandate%20from%20transaction%20where%20type%3D'SalesOrd'
var id = context.id;
var suiteQL = context.suiteQL;
// Run the query
var suiteQLResults = query.runSuiteQL(suiteQL);
// Compose the RESTlet response
var response = {
id: id,
suiteQL: suiteQL,
resultSuiteQL: suiteQLResults.asMappedResults()
};
// Return the response
return JSON.stringify(response);
};
});
Run a Paged SuiteQL Query
This example script runs a SuiteQL query as a paged query with a page size of 10 results per page.
/**
* @NApiVersion 2.x
*/
require(['N/query'], function(query) {
// Construct the SuiteQL query string
var sql =
"SELECT " +
" scriptDeployment.primarykey, scriptexecutioncontextmap.executioncontext " +
" FROM " +
" scriptDeployment, scriptexecutioncontextmap " +
" WHERE " +
" scriptexecutioncontextmap.scriptrecord = scriptDeployment.primarykey " +
" AND " +
" scriptexecutioncontextmap.executioncontext IN ('WEBSTORE', 'WEBAPPLICATION')";
// Run the SuiteQL query as a paged query and return an iterator
var resultIterator = query.runSuiteQLPaged({
query: sql,
pageSize: 10
}).iterator();
// Use the iterator to process each page of results
resultIterator.each(function(page) {
var pageIterator = page.value.data.iterator();
pageIterator.each(function(row) {
log.debug('ID: ' + row.value.getValue(0) + ', Context: ' + row.value.getValue(1));
return true;
});
return true;
});
});
Use a SuiteQL Query in a Map/Reduce Script
This example map/reduce script uses a SuiteQL query as the source of input data. The value 271 is the internal ID of a transaction record.
/**
* @NApiVersion 2.x
* @NScriptType mapreducescript
*/
define(['N/query'], function(query) {
// Define the getInputData() stage function
function getInputData() {
// Construct the SuiteQL query string
var suiteQL =
"SELECT " +
" \"TRANSACTION\".tranid AS tranidRAW /*{tranid#RAW}*/ , " +
" \"TRANSACTION\".trandate AS trandateRAW /*{trandate#RAW}*/, " +
" \"TRANSACTION\".postingperiod AS postingperiodDISPLAY /*{postingperiod#DISPLAY}*/ " +
"FROM " +
" \"TRANSACTION\" WHERE \"TRANSACTION\".\"ID\" = ? ";
// Return the query results as input data. The value 271 is the
// internal ID of a transaction record
return {
type: 'suiteql',
query: suiteQL,
params: [271]
};
}
// Define the map() stage function
function map(context) {
context.write(context.key, context.value);
}
// Define the reduce() stage function
function reduce(context) {
context.write(context.key, context.values[0]);
}
// Define the summarize() stage function
function summarize(summary) {
if (summary.inputSummary.error) {
log.debug('An error occurred.');
}
}
// Return the function definitions
return {
getInputData: getInputData,
map: map,
reduce: reduce,
summarize: summarize
}
});