Examples

SuiteScript SuiteQL Query

Running a SuiteQL Query

SuiteScript SuiteQL query uses N/query for analytics data.

Introduction to SuiteQL

SuiteQL is an extension of SQL that provides a robust and flexible way to access NetSuite data for analytics purposes. It is part of the SuiteScript 2.0 framework and allows users to perform complex queries using the N/query module. SuiteQL is particularly useful for extracting and analyzing data that may not be easily accessible through standard NetSuite search capabilities.

Setting Up Your Environment

Before you begin using SuiteQL, ensure that you have the necessary permissions and access to SuiteScript 2.0. You will need to enable the SuiteScript and SuiteAnalytics features in your NetSuite account. Additionally, familiarity with basic SQL syntax will be beneficial.

Basic SuiteQL Query Structure

A SuiteQL query is similar to a standard SQL query but tailored for NetSuite's data model. Here's a basic example of a SuiteQL query:

This query selects the id and name columns from the customer table where the customer is active. SuiteQL supports standard SQL clauses such as SELECT, FROM, WHERE, ORDER BY, and more.

Executing SuiteQL Queries with N/query

To execute SuiteQL queries, use the N/query module in SuiteScript 2.0. Below is an example of how to execute a SuiteQL query using this module:

In this example, the runSuiteQL method is used to execute the query. The results are iterated over, and each customer's ID and Name are logged. This pattern is common when working with SuiteQL in SuiteScript.

Handling Complex Queries

SuiteQL allows for complex queries involving joins, aggregations, and subqueries. Here is an example of a more complex SuiteQL query:

This query retrieves the ID and name of each active customer along with a count of their associated sales orders. The use of LEFT JOIN and GROUP BY demonstrates SuiteQL's ability to handle complex data retrieval requirements.

Limitations and Considerations

While SuiteQL is powerful, it has limitations. Not all SQL features are supported, and the performance of queries can vary depending on data complexity and volume. It's essential to test queries thoroughly and optimize them for performance.

Examples

Previous: Search Query
Next: API Integration