1 What is SuiteQL

SuiteQL is a query language based on the SQL-92 revision of the SQL database query language.

2 How to Execute SuiteQL

There are two methods to execute SuiteQL queries: using the N/query module in SuiteScript and the SuiteTalk REST Web Service.

2.1 SuiteScript

Use the query.runSuiteQL() function from the N/query module to run SuiteQL. Example code:

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

Example output:

[
    { tranid: 'SO001', trandate: '2024/01/01' },
    { tranid: 'SO002', trandate: '2024/01/02' },
    { tranid: 'SO003', trandate: '2024/01/03' },
    { tranid: 'SO004', trandate: '2024/01/04' }
]

2.2 SuiteTalk REST Web Service

Execute SuiteQL queries through the REST Web Service.

• URL: https://{accountId}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql, where {accountId} is the account ID, found at: Setup > Company > Company Information.
• Method: POST
• Body: { "q" : queryStatement }, where queryStatement is the query string.

Example code:

curl -i -X POST 'https://{accountId}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql' \
-H 'Content-Type: application/json' \
-H 'Authorization: *******************************' \
-d { "q" : "SELECT transaction.tranid, transaction.trandate from transaction where rownum < 10"}

Example response:

{
  "links": [
    {
      "rel": "self",
      "href": "https://{accountId}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql"
    }
  ],
  "count": 4,
  "hasMore": false,
  "items": [
    {
      "links": [],
      "tranid": "SO001",
      "trandate": "2024/01/01",
      "id": "221"
    },
    {
       "links": [],
      "tranid": "SO002",
      "trandate": "2024/01/02",
      "id": "222"
    },
    {
       "links": [],
      "tranid": "SO003",
      "trandate": "2024/01/03",
      "id": "223"
    },
    {
       "links": [],
      "tranid": "SO004",
      "trandate": "2024/01/04",
      "id": "224"
    }
  ],
  "offset": 0,
  "totalResults": 4
}

3 SuiteQL Table Structure

NetSuite does not provide an explicit table structure. Common tables include transactions, transaction lines, and entities. Although there is no clear table structure, you can use the record directory to look up common tables and they field information. Path: Setup > Record Directory.

4 SuiteQL Syntax and Parameters

SuiteQL supports SQL-92 and Oracle SQL syntax but cannot use both in the same query. Below is a reference for syntax:

• SQL-92 Language Reference
• Oracle Database SQL Language Reference

SuiteQL has two parameter-passing methods:

1. Dynamic parameters are supported in SuiteQL. Use question marks in the query statement and pass the parameter array 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);
  }

// Sales Order
getTransactionsByType('SalesOrd');
// Invoice
getTransactionsByType('CustInvc');

2. String concatenation

  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);
  }

// Sales Order
getTransactionsByType('SalesOrd');
// Invoice
getTransactionsByType('CustInvc');

5 Pros and Cons

Before SuiteQL, we used saved search queries. What are the advantages and disadvantages of SuiteQL compared to saved searches?

Advantages:

1. Faster data retrieval
2. Multi-table joins

Disadvantages:

1. Cannot be used in UI
2. More complex syntax with a higher learning curve