SQL and SuiteQL of Income Statement Workbook

Dataset:

Workbook:

SQL:

SELECT 
  BUILTIN_RESULT.TYPE_STRING(BUILTIN.DF(TransactionAccountingLine.ACCOUNT)) AS ACCOUNT, 
  BUILTIN_RESULT.TYPE_CURRENCY(BUILTIN.CONSOLIDATE(TransactionAccountingLine.amount, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 534, 'DEFAULT'), BUILTIN.CURRENCY(BUILTIN.CONSOLIDATE(TransactionAccountingLine.amount, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 534, 'DEFAULT'))) AS amount
FROM 
  TRANSACTION, 
  ACCOUNT, 
  TransactionAccountingLine, 
  transactionLine
WHERE 
  (((TransactionAccountingLine.ACCOUNT = ACCOUNT.ID(+) AND (transactionLine.TRANSACTION = TransactionAccountingLine.TRANSACTION AND transactionLine.ID = TransactionAccountingLine.transactionline)) AND TRANSACTION.ID = transactionLine.TRANSACTION))
   AND ((ACCOUNT.accttype IN ('COGS', 'Expense', 'Income', 'OthExpense', 'OthIncome') AND TRANSACTION.posting = ?))

SuiteQL:

var param0 = ?;
var sql = "SELECT BUILTIN_RESULT.TYPE_STRING(BUILTIN.DF(TransactionAccountingLine.ACCOUNT)) AS ACCOUNT, BUILTIN_RESULT.TYPE_CURRENCY(BUILTIN.CONSOLIDATE(TransactionAccountingLine.amount, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 534, 'DEFAULT'), BUILTIN.CURRENCY(BUILTIN.CONSOLIDATE(TransactionAccountingLine.amount, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 534, 'DEFAULT'))) AS amount FROM TRANSACTION, ACCOUNT, TransactionAccountingLine, transactionLine WHERE (((TransactionAccountingLine.ACCOUNT = ACCOUNT.ID(+) AND (transactionLine.TRANSACTION = TransactionAccountingLine.TRANSACTION AND transactionLine.ID = TransactionAccountingLine.transactionline)) AND TRANSACTION.ID = transactionLine.TRANSACTION)) AND ((ACCOUNT.accttype IN ('COGS', 'Expense', 'Income', 'OthExpense', 'OthIncome') AND TRANSACTION.posting = ?)) ";

/*********************************** */

// Run the query
var resultSet = query.runSuiteQL({query: sql,params:[param0]});
var results = resultSet.results;
for (var i = results.length - 1; i >= 0; i--)
  log.debug(results[i].values);

var resultsObj = resultSet.asMappedResults();
log.debug("Mapped Results",resultsObj);

/*********************************** */

// Run the query as a paged query
var results = query.runSuiteQLPaged({query: sql,params:[param0],pageSize: 50});
for (var i = 0; i < results.pageRanges.length; i++)  {
  var page = results.fetch(i);
  log.debug(page.pageRange.index,page.pageRange.size);
  var pageResults = page.data.results;
  for (var r = 0; r < pageResults.length; r++)
    log.debug(pageResults[r].values);
}

/*********************************** */

// Run the query as a paged query
// Retrieve the query results using an iterator
var results = query.runSuiteQLPaged({query: sql,params:[param0],pageSize: 50});
var resultIterator = results.iterator();
resultIterator.each(function(page) {
  log.debug(page.value.pageRange.index,page.value.pageRange.size);
  var pageIterator = page.value.data.iterator();
  pageIterator.each(function(row) {
    log.debug(row.value.values);
    return true;
  });
  return true;
});