Using SuiteQL for Efficient Data Queries in NetSuite

What is SuiteQL?

SuiteQL (SuiteAnalytics Query Language) is a query language introduced by NetSuite that allows you to retrieve data using SQL-like syntax. It provides greater flexibility and performance compared to standard saved searches and N/query module in SuiteScript.

Why Use SuiteQL?

Performance: Queries execute faster compared to saved searches.
Advanced Joins: Supports complex joins across multiple tables.
Aggregations & Filtering: Allows use of SQL functions like COUNT, SUM, GROUP BY, etc.
Greater Data Access: Enables retrieval of fields that are not available in saved searches.

How to Use SuiteQL in SuiteScript

SuiteQL can be used in SuiteScript 2.x via the N/query module.

1. Basic SuiteQL Query Example

Fetching customer data:

javascript

Copy

Edit
define(['N/query'], function(query) {
    function executeSuiteQL() {
        var sqlQuery = `
            SELECT id, entityid, email, subsidiary 
            FROM customer 
            WHERE email IS NOT NULL 
            ORDER BY entityid ASC
        `;

        var resultSet = query.runSuiteQL({ query: sqlQuery });
        var results = resultSet.asMappedResults(); // Converts to JSON format

        log.debug('Customer Data:', results);
        return results;
    }

    return { executeSuiteQL: executeSuiteQL };
});

💡 Key Points:

query.runSuiteQL() executes the SQL query.
asMappedResults() converts the result into an array of objects.

2. Joining Multiple Tables

SuiteQL allows joining multiple tables, unlike saved searches.

Example: Fetching Sales Orders with Customer Details

javascript

Copy

Edit
var sqlQuery = `
    SELECT so.id AS sales_order_id, so.tranid, c.entityid AS customer_name, so.total 
    FROM transaction AS so 
    INNER JOIN customer AS c ON so.entity = c.id 
    WHERE so.type = 'SalesOrd' 
    ORDER BY so.trandate DESC
`;
var results = query.runSuiteQL({ query: sqlQuery }).asMappedResults();
log.debug('Sales Orders:', results);

3. Using Aggregations (SUM, COUNT, GROUP BY)

To get the total sales per customer:

javascript

Copy

Edit
var sqlQuery = `
    SELECT c.entityid AS customer_name, SUM(so.total) AS total_sales
    FROM transaction AS so
    INNER JOIN customer AS c ON so.entity = c.id
    WHERE so.type = 'SalesOrd' 
    GROUP BY c.entityid
`;
var results = query.runSuiteQL({ query: sqlQuery }).asMappedResults();
log.debug('Total Sales Per Customer:', results);

✔️ SuiteQL supports aggregate functions like SUM, COUNT, MAX, etc.

4. Filtering Data with WHERE Clause

Fetching invoices created in the last 30 days:

javascript

Copy

Edit
var sqlQuery = `
    SELECT id, tranid, total 
    FROM transaction 
    WHERE type = 'Invoice' AND trandate >= ADD_MONTHS(CURRENT_DATE, -1)
`;
var results = query.runSuiteQL({ query: sqlQuery }).asMappedResults();
log.debug('Recent Invoices:', results);

5. Handling Large Data Sets with LIMIT & OFFSET

NetSuite SuiteQL does not allow direct pagination, but you can use LIMIT and OFFSET for large queries.

javascript

Copy

Edit
var sqlQuery = `
    SELECT id, entityid, email 
    FROM customer 
    ORDER BY id ASC 
    LIMIT 100 OFFSET 200
`;

📌 LIMIT 100 OFFSET 200 retrieves records 201 to 300.