SuiteQL: Introduction

Introduction

This post is an introduction to SuiteQL, and more specifically an introduction to this blog series which will discuss SuiteQL.

SuiteQL, released in 2021 is a new way of querying data from NetSuiteusing traditional SQL paradigms.

It’s based on the Ansi-92 standard and is missing some modern features. For more information about what is supported please visit the Oracle Documentation You are also able to write queries using some Oracle specific modern features such as WITH clauses.

How to run SuiteQL

There are three ways to run SuiteQL queries.

First, you can run SuiteQL using SuiteScript. NetSuite provides the N/query module to run SuiteQL queries. In particular the function query.runSuiteQL() is used to run SuiteQL queries. The function asMappedResults() can be used to get the results as a javascript object.

Below is a simple example SuiteQL running in a scheduled script

/**
 *@NApiVersion 2.1
 *@NScriptType ScheduledScript
 */
define(['N/query'],
function(query){
    return {
        execute: () => {
            const queryObj = query.runSuiteQL(`
            SELECT transaction.tranid, transaction.trandate from transaction where rownum < 10
            `)
            const results = queryObj.asMappedResults();
            log.debug('results', results);
        }
    }
}
)

The results will look like so:

[
    { tranid: 'SO123', trandate: '01/01/2021' },
    { tranid: 'SO124', trandate: '01/02/2021' },
    { tranid: 'SO125', trandate: '01/03/2021' },
    { tranid: 'SO126', trandate: '01/04/2021' },
    ...
]

Second, you can run SuiteQL using the SuiteTalk API. The path for SuiteQL using your rest api endpoint is /services/rest/query/v1/suiteql. You can find you rest api endpoint by going to Setup > Company > Company Information in the Company URLs subtab. Make sure to enable the rest api in Setup > Company > Enable Features.

For an example of using the SuiteTalk API to run SuiteQL queries please see this gist by Michoel Chaikin.

Third, you can run SuiteQL using SuiteAnalytics connect with the ODBC connection. The ODBC connection must be purchased separately. If you want to use the same schema in SuiteAnalytics as you do in SuiteQL you will need to use the Netsuite2.com data source. See the documentation here for more information.

One important thing to not about running queries using SuiteAnalytics is that you can only run SQL-92 queries without any modern Oracle features. Most notably you cannot use WITH clauses.

SuiteQL Schema

The schema in SuiteQL can be a bit of a mess. Some important tables are transaction, transactionline, item, entity.

The transaction table refers to mainline fields such as the transaction date, or customer. The transactionline table refers to sublist fields such as the item or quantity. This will be discussed in more detail in a separate post.

To understand the schema and to prototype queries I highly recommend using Tim Dietrich’s SuiteQL tool.

Many fields have the value of an internal id of another record. For example the field transactionline.item will just be the internal id of the item record. To get the name of the item you will either need to join the item table or use the BUILTIN.DF function as discussed in the next section.

Parameters

SuiteQL supports parameters which can be used to make your queries more dynamic.

Leave question marks in your query where you want to use parameters and then pass an array of parameters to the runSuiteQL function.

  function getTransactionsByType(type) {
    const queryObj = query.runSuiteQL(`
    SELECT transaction.tranid, transaction.trandate from transaction where type = ? and rownum < 10
    `, [type])
    const results = queryObj.asMappedResults();
    log.debug('results', results);
  }

Now we can call the function for sales orders: getTransactionsByType('SalesOrd') or for invoices: getTransactionsByType('CustInvc')

We can also use javascript string interpolation to make the code more readable.

  function getTransactionsByType(type) {
    const queryObj = query.runSuiteQL(`
    SELECT transaction.tranid, transaction.trandate from transaction where type = '${type}' and rownum < 10
    `)
    const results = queryObj.asMappedResults();
    log.debug('results', results);
  }

This is the convention I use in the examples in this blog series.

Built in Functions

NetSuite exposes a few special built in functions to help you with querying data.

BUILTIN.DF

This function is used to get the text representation of a field in NetSuite. For example if I wanted to get the employee who created a record I might run:

SELECT createdby FROM transaction where rownum < 2

But this would return the internal id of an employee instead of his name. If I wanted {Firstname} {LastName} as it shows in the UI I would instead write :

SELECT BUILTIN.DF(createdby) as createdby from transaction where rownum < 2

This is the most important builtin function and is used in many queries.

BUILTIN.HIERARCHY

This function is used to get the hierarchy of certain types of fields, namely accounts. So if I write a query

SELECT BUILTIN.DF(account) as account from TransactionAccountingLIne where rownum < 2

I might get a result like 4200: Revenue Sales

But if I run

SELECT BUILTIN.HIERARCHY(account, 'DISPLAY_JOINED') as account from TransactionAccountingLIne where rown < 2

I’ll get the full hierarchy such as 4000 Revenue: 4200 Revenue Sales

BUILTIN.RELATIVE_RANGES

This function is used to get the ranges of a date field that is relative to the current date.

For example if I wanted to get the start and end date of the last quarter I could write:

SELECT transaction.tranid
FROM transaction WHERE transaction.trandate BETWEEN
BUILTIN.RELATIVE_RANGES('LAST_FISCAL_QUARTER', 'START') and
BUILTIN.RELATIVE_RANGES('LAST_FISCAL_QUARTER', 'END')

There are other built in functions; for a complete list please visit the Oracle Documentation

SuiteQL vs Saved Searches

SuiteQL provides vast advantages over saved searches.

While traditional saved searches use verbose language and complex arrays for filtering, SuiteQL is much more straightforward. If you are even a little familiar with SQL, you will be able to pick up SuiteQL very quickly.

A common limitation of saved searches is that saved searches only allow one level of joining. Suppose I want the cost of the components of a bill of materials listed on a work order; in SuiteQL I can join the BOM and join the components and join the item table to get the cost but saved searches will be limited to joining the bill of materials.

SuiteQL is not without drawbacks. For one, SuiteQL is not available in the UI. I use Tim Dietrich’s SuiteQL tool to prototype queries and investigate NetSuite’s schema but if you’ll want to create a report for an end user you will have to create your own suitelet.

But as far as replacing saved searches running in suitescript I highly recommend it.