SuiteScript SuiteQL Syntax

Introduction to SuiteScript SuiteQL

SuiteScript SuiteQL is a powerful querying language that allows developers to interact with NetSuite's data by utilizing SQL-92 syntax. This enables the execution of complex queries directly within NetSuite, leveraging its vast array of tables and fields.

Basic Syntax

SuiteQL supports the basic SQL-92 query structure, which includes the SELECT, FROM, WHERE, GROUP BY, ORDER BY, and HAVING clauses. Here's a simple example:

In this query, we are selecting the id and name fields from the customer table where the customer is active, and ordering the results by the customer's name in ascending order.

Using Functions in SuiteQL

SuiteQL supports a variety of SQL functions to perform operations on data. These include aggregation functions like SUM, AVG, MAX, and string functions like CONCAT, SUBSTRING. For example:

This query concatenates the firstname and lastname fields to create a fullname. It then calculates the total order amount for each customer, grouping by their full name, and only returns those customers whose total order amount exceeds 1000.

Working with NetSuite-Specific Tables

When working with SuiteQL, it is essential to understand the structure and naming conventions of NetSuite tables. Each table represents a specific type of record, such as customer, salesorder, or transaction. You can use NetSuite's schema browser to explore these tables and their fields for constructing your queries.

Here's an example that demonstrates how to query a NetSuite-specific table:

This query retrieves the id, date, and amount for all records in the transaction table where the type is 'Invoice' and the date is on or after January 1, 2023.

Conclusion

Understanding and utilizing SuiteScript SuiteQL syntax allows developers to efficiently access and manipulate data within NetSuite. By leveraging SQL-92 standards, SuiteQL provides a familiar and robust environment for performing complex data operations. In the next post, we will explore how to perform joins in SuiteQL to combine data from multiple tables.