# How to write and run SuiteQL queries in NetSuite

> Source: https://netray.co/erp-help/netsuite-how-to-write-suiteql-queries
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## Short answer

SuiteQL is NetSuite's read-only SQL dialect over the underlying record tables, run either interactively from Analytics > SuiteQL Query Tool (or the older /app/suiteanalytics query page), through REST at /services/rest/query/v1/suiteql, or programmatically via the N/query module in SuiteScript 2.x. It supports standard SELECT, JOIN, WHERE, GROUP BY and window functions against table names that mostly match record type IDs (transaction, transactionline, item, customer).

## Kind

how-to

## System

netsuite

## Erp

Oracle NetSuite

## Module

SuiteQL / SuiteAnalytics

## Query

how to write SuiteQL queries in NetSuite

## Alternate Queries

- netsuite suiteql query examples
- how to run suiteql in netsuite
- netsuite suiteql N/query module
- netsuite suiteql vs saved search

## Applies To

NetSuite 2020.2 and later (SuiteQL Query Tool bundle and REST endpoint); N/query available since SuiteScript 2.1. Requires the SuiteAnalytics Workbook feature and appropriate role permissions.

## Steps

### Run a SuiteQL query

**Items:**

- Enable SuiteAnalytics Workbook (Setup > Company > Enable Features > Analytics) if the query tool is not visible.
- Go to Analytics > SuiteQL Query Tool (or search for the SuiteQL Query Tool bundle 665142 if not installed) to query interactively.
- Write a standard SELECT against a NetSuite table, e.g. SELECT id, tranid, trandate FROM transaction WHERE type = 'SalesOrd' AND trandate >= TO_DATE('2026-01-01','YYYY-MM-DD').
- Join to related tables with the actual join column, not the saved search dot-notation, e.g. JOIN transactionline ON transactionline.transaction = transaction.id.
- For scripted use, call query.runSuiteQL({ query: sql, params: [] }) from N/query and iterate .asMappedResults() or .results.
- For REST/external tools, POST the SQL text as JSON to /services/rest/query/v1/suiteql with header Prefer: transient (or paginate via the Content-Type: application/vnd.oracle.resource+json header) using OAuth 1.0 TBA or OAuth 2.0.
- Paginate large result sets with LIMIT and OFFSET or the REST endpoint's built-in offset parameter - SuiteQL does not auto-page in the query tool UI beyond the default limit.
- Watch governance: N/query counts against SuiteScript usage units, and the query tool UI caps result rows (commonly 4,000-5,000) even if more rows match.

## Sections

### SuiteQL table and column names differ from saved search field IDs

**Body:**

Saved search field IDs (like {tranid} or {entity}) do not map one-to-one onto SuiteQL column names. Most transaction body fields live on the transaction table, line-level fields on transactionline, and the two are joined via transaction.id = transactionline.transaction. Custom fields keep their script ID as the column name (custbody_x, custcol_x, custentity_x, custrecord_x) but only appear if selected explicitly - SELECT * is not supported the way it is in plain SQL for most tables.

BUILTIN.DF works in SuiteQL the same way it does in saved search formulas, converting an internal ID/list value into its display text. For record type names, use BUILTIN.DF(transaction.type) rather than trying to join to a system list table.

**Code:**

SELECT
  t.tranid,
  t.trandate,
  BUILTIN.DF(t.entity) AS customer,
  tl.item,
  tl.quantity,
  tl.netamount
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type = 'SalesOrd'
  AND t.trandate >= TO_DATE('2026-01-01','YYYY-MM-DD')
ORDER BY t.trandate DESC

### Running SuiteQL from a SuiteScript with N/query

**Body:**

N/query.runSuiteQL() takes an object with a query string and an optional params array for bind variables (use ? placeholders instead of concatenating values into the SQL string, both for performance and to avoid injection-style bugs). The result exposes .asMappedResults() for an array of plain objects keyed by column alias, which is usually easier to work with than the raw .results array of Result objects.

SuiteQL through N/query is subject to the same governance limits as any other SuiteScript API call and to the platform's SQL execution time limits, so very large joins should be paginated with LIMIT/OFFSET inside a scheduled script rather than pulled in one call from a user event or RESTlet.

**Code:**

var query.runSuiteQL is called like:
var sql = 'SELECT id, tranid FROM transaction WHERE type = ? AND trandate >= ?';
var results = query.runSuiteQL({ query: sql, params: ['SalesOrd', '1/1/2026'] }).asMappedResults();

### REST query endpoint for external BI tools

**Body:**

The /services/rest/query/v1/suiteql endpoint accepts a POST body of { "q": "SELECT ..." } and returns JSON, making it the standard integration point for external tools that are not using SuiteAnalytics Connect ODBC. Authentication is OAuth 1.0 Token-Based Authentication or OAuth 2.0 client credentials/authorization code, same as RESTlets. Set the Prefer header to transient to avoid NetSuite creating a saved query record for every call.

Pagination on the REST endpoint uses offset and limit in the URL or request; the response includes hasMore and totalResults so a client can loop until hasMore is false. This is the pattern most integration middleware (Boomi, Celigo, custom scripts) uses to pull incremental data out of NetSuite via SuiteQL rather than SOAP SuiteTalk searches.

### When to use SuiteQL over a saved search

**Body:**

SuiteQL is the better choice once a report needs joins saved search cannot express, window functions (RANK, SUM OVER PARTITION BY), UNION across record types, or performance at scale for scheduled/API consumption. Saved search remains better for anything end users need to build or modify themselves through the UI, since SuiteQL requires SQL literacy and either the query tool, a script, or an external BI connection to run.

## Pitfalls

- Assuming SELECT * works uniformly - most tables require explicit column lists, and custom fields must be named individually.
- Confusing saved search field IDs with SuiteQL column names; they are not interchangeable across the two systems.
- Not paginating large result sets, hitting the query tool's row cap or REST default limit and assuming that is the full dataset.
- Concatenating user input into SQL strings instead of using bind parameters with N/query.
- Running heavy SuiteQL joins synchronously in a user event script and hitting governance or timeout limits.
- Forgetting the Prefer: transient header on REST calls, leaving orphaned saved query records behind.

## Ai Angle

ERPray can translate a plain-language reporting question - "total netamount by item for sales orders last quarter" - directly into a SuiteQL statement against the account's real table and custom field names, including the transaction/transactionline join, and can execute it through N/query or the REST endpoint on request. For scripted use it still helps to have a developer confirm governance impact before scheduling it to run frequently.

## Faq

### Is SuiteQL read-only?

**Answer:** Yes. SuiteQL only supports SELECT statements; inserts, updates and deletes must go through Record/SuiteScript, SOAP SuiteTalk, or the REST record API, not SuiteQL.

### What is the row limit for SuiteQL results?

**Answer:** The SuiteQL Query Tool UI typically caps displayed rows (around 4,000-5,000 depending on version); N/query and the REST endpoint should be paginated with LIMIT/OFFSET or the offset parameter to retrieve full result sets beyond that.

### Can SuiteQL query custom records?

**Answer:** Yes. Custom record types are queryable by their internal table name (usually customrecord_xxx) with custom fields as columns, the same as standard records.

### Does SuiteQL support window functions like RANK or SUM OVER?

**Answer:** Yes, SuiteQL supports a substantial subset of Oracle analytic SQL including window functions, which saved search formula fields do not support - this is one of the main reasons to move a report from saved search to SuiteQL.

## Search terms

netsuite suiteql, netsuite suiteql query tool, netsuite n/query module, netsuite suiteql rest api, netsuite suiteql join transactionline, netsuite suiteql pagination, netsuite suiteql vs saved search, netsuite suiteql window functions, netsuite suiteql custom record, netsuite analytics suiteql

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json