Skip to main content

overview

Query (SuiteQL) Overview

What is SuiteQL?#

SuiteQL is NetSuite's SQL-based query language that allows you to write standard SQL queries against your NetSuite data. It provides more flexibility and power than Saved Search for complex data analysis.

Why Use SuiteQL?#

Advantages Over Saved Search#

Standard SQL Syntax:

  • Familiar to database professionals
  • Industry-standard language
  • Portable knowledge

More Powerful:

  • Complex joins across multiple tables
  • Subqueries and CTEs (Common Table Expressions)
  • Advanced aggregations
  • Window functions
  • Set operations (UNION, INTERSECT, EXCEPT)

Better for Developers:

  • Code-based queries
  • Version control friendly
  • Easier to maintain
  • Parameterizable
  • Programmatic access

Performance:

  • Often faster for complex queries
  • Better optimization
  • Direct database access

When to Use SuiteQL vs Saved Search#

Use SuiteQL When:

  • Complex multi-table joins needed
  • Advanced SQL features required
  • Building integrations
  • Developer-centric approach
  • Need for subqueries or CTEs
  • Complex aggregations
  • Set operations needed

Use Saved Search When:

  • Simple queries
  • UI-based search preferred
  • Non-technical users
  • Dashboard integration
  • Standard reporting
  • Quick ad-hoc analysis

Accessing SuiteQL#

Query Tool#

Location:

  • Go to Reports > Saved Searches > New
  • Or use SuiteAnalytics Workbook
  • Or access via REST API

SuiteAnalytics Workbook#

Features:

  • Visual query builder
  • SQL editor
  • Query testing
  • Result preview
  • Save and share queries

REST API#

Endpoint:

POST https://{account}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql

Authentication:

  • OAuth 2.0
  • Token-based authentication

NetSuite Data Model#

Understanding Tables#

NetSuite data is organized in tables (called "record types" in NetSuite UI):

Transaction Tables:

  • Transaction - All transactions
  • TransactionLine - Transaction line details
  • TransactionAccountingLine - Accounting entries

Entity Tables:

  • Customer - Customer records
  • Vendor - Vendor records
  • Employee - Employee records
  • Contact - Contact records

Item Tables:

  • Item - All items
  • InventoryItem - Inventory items specifically
  • ItemVendor - Item-vendor relationships

Lists and References:

  • Account - Chart of accounts
  • Department - Departments
  • Location - Locations
  • Class - Classifications

Table Names#

NetSuite tables use CamelCase:

Transaction (not transaction)Customer (not customer)TransactionLine (not transaction_line)

Field Names#

Fields also use lowercase with underscores:

idtrandateentityamount

Basic Query Structure#

SELECT Statement#

SELECT     fieldname1,    fieldname2,    fieldname3FROM     TableNameWHERE     conditionORDER BY     fieldname

Simple Example#

Get Open Sales Orders:

SELECT     tranid AS order_number,    trandate AS order_date,    entity AS customer_id,    amountFROM     TransactionWHERE     type = 'SalesOrd'    AND status IN ('SalesOrd:A', 'SalesOrd:B')ORDER BY     trandate DESC

Key Concepts#

Internal IDs#

Every record has a unique internal ID:

SELECT     id,              -- Internal ID    tranid,          -- Document number (SO-12345)    entity           -- Customer internal IDFROM     Transaction

Record Type Filtering#

Always filter by record type:

WHERE type = 'SalesOrd'     -- Sales OrderWHERE type = 'CustInvc'     -- InvoiceWHERE type = 'VendBill'     -- Bill

Status Values#

Status uses internal names:

-- Sales Order statuses'SalesOrd:A'  -- Pending Approval'SalesOrd:B'  -- Pending Fulfillment  'SalesOrd:C'  -- Partially Fulfilled'SalesOrd:D'  -- Pending Billing'SalesOrd:E'  -- Pending Billing/Part Fulfilled'SalesOrd:F'  -- Billed'SalesOrd:G'  -- Closed'SalesOrd:H'  -- Cancelled
-- Invoice statuses'CustInvc:A'  -- Open'CustInvc:B'  -- Paid In Full

Date Filtering#

-- Specific dateWHERE trandate = TO_DATE('2025-01-01', 'YYYY-MM-DD')
-- Date rangeWHERE trandate BETWEEN     TO_DATE('2025-01-01', 'YYYY-MM-DD')     AND TO_DATE('2025-12-31', 'YYYY-MM-DD')
-- Relative dates (using BUILTIN functions)WHERE trandate >= BUILTIN.RELATIVE_RANGES('TM', 'START')-- TM = This Month, TQ = This Quarter, TY = This Year

Common Queries#

Customer List#

SELECT     id,    companyname,    email,    phone,    salesrep,    creditlimit,    balanceFROM     CustomerWHERE     isinactive = 'F'ORDER BY     companyname

Invoice Summary#

SELECT     TO_CHAR(trandate, 'YYYY-MM') AS month,    COUNT(*) AS invoice_count,    SUM(amount) AS total_amount,    AVG(amount) AS avg_amountFROM     TransactionWHERE     type = 'CustInvc'    AND EXTRACT(YEAR FROM trandate) = 2025GROUP BY     TO_CHAR(trandate, 'YYYY-MM')ORDER BY     month

Items Below Reorder Point#

SELECT     itemid,    displayname,    quantityavailable,    reorderpoint,    (reorderpoint - quantityavailable) AS qty_neededFROM     InventoryItemWHERE     quantityavailable <= reorderpoint    AND isinactive = 'F'ORDER BY     qty_needed DESC

Joins#

Basic Join#

SELECT     t.tranid,    t.trandate,    c.companyname,    t.amountFROM     Transaction tINNER JOIN     Customer c ON t.entity = c.idWHERE     t.type = 'SalesOrd'

Multiple Joins#

SELECT     t.tranid,    c.companyname,    e.entityid AS sales_rep,    l.name AS locationFROM     Transaction tINNER JOIN Customer c ON t.entity = c.idLEFT JOIN Employee e ON c.salesrep = e.idLEFT JOIN Location l ON t.location = l.idWHERE     t.type = 'CustInvc'

Aggregations#

GROUP BY#

SELECT     entity,    COUNT(*) AS order_count,    SUM(amount) AS total_sales,    AVG(amount) AS avg_order_valueFROM     TransactionWHERE     type = 'SalesOrd'    AND EXTRACT(YEAR FROM trandate) = 2025GROUP BY     entityHAVING     SUM(amount) > 50000ORDER BY     total_sales DESC

Window Functions#

SELECT     tranid,    trandate,    amount,    SUM(amount) OVER (        ORDER BY trandate         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW    ) AS running_total,    ROW_NUMBER() OVER (        PARTITION BY entity         ORDER BY amount DESC    ) AS rank_by_customerFROM     TransactionWHERE     type = 'CustInvc'

SuiteQL Features#

Common Table Expressions (CTEs)#

WITH monthly_sales AS (    SELECT         TO_CHAR(trandate, 'YYYY-MM') AS month,        SUM(amount) AS sales    FROM         Transaction    WHERE         type = 'CustInvc'        AND EXTRACT(YEAR FROM trandate) = 2025    GROUP BY         TO_CHAR(trandate, 'YYYY-MM'))SELECT     month,    sales,    sales - LAG(sales) OVER (ORDER BY month) AS month_over_month_changeFROM     monthly_salesORDER BY     month

Subqueries#

SELECT     companyname,    (        SELECT SUM(amount)        FROM Transaction        WHERE entity = Customer.id        AND type = 'CustInvc'        AND EXTRACT(YEAR FROM trandate) = 2025    ) AS total_invoiced_2025FROM     CustomerWHERE     isinactive = 'F'ORDER BY     total_invoiced_2025 DESC

UNION#

SELECT 'Customer' AS entity_type, companyname AS name, emailFROM CustomerWHERE isinactive = 'F'
UNION ALL
SELECT 'Vendor' AS entity_type, companyname AS name, emailFROM VendorWHERE isinactive = 'F'
ORDER BY entity_type, name

Best Practices#

Query Design#

Be Specific:

-- GoodSELECT id, tranid, amountFROM TransactionWHERE type = 'SalesOrd'
-- AvoidSELECT *FROM Transaction

Use Aliases:

SELECT     t.tranid AS order_number,    c.companyname AS customer_nameFROM Transaction tINNER JOIN Customer c ON t.entity = c.id

Filter Early:

-- Good: Filter before joinSELECT t.tranid, c.companynameFROM (    SELECT * FROM Transaction     WHERE type = 'SalesOrd'     AND trandate >= '2025-01-01') tINNER JOIN Customer c ON t.entity = c.id
-- Less efficient: Filter after joinSELECT t.tranid, c.companynameFROM Transaction tINNER JOIN Customer c ON t.entity = c.idWHERE t.type = 'SalesOrd' AND t.trandate >= '2025-01-01'

Performance#

Index Usage:

  • Filter on indexed fields (id, type, status, date)
  • Avoid functions on indexed columns in WHERE clause

Limit Results:

SELECT *FROM TransactionWHERE type = 'SalesOrd'LIMIT 1000

Use EXISTS Instead of IN:

-- Better performanceWHERE EXISTS (    SELECT 1 FROM TransactionLine     WHERE transaction = Transaction.id)
-- SlowerWHERE id IN (    SELECT transaction FROM TransactionLine)

Common Use Cases#

Financial Reporting#

Aged Receivables:

SELECT     c.companyname,    t.tranid,    t.trandate,    t.duedate,    t.amount,    t.amountremaining,    CURRENT_DATE - t.duedate AS days_overdue,    CASE         WHEN CURRENT_DATE - t.duedate <= 0 THEN 'Current'        WHEN CURRENT_DATE - t.duedate <= 30 THEN '1-30 Days'        WHEN CURRENT_DATE - t.duedate <= 60 THEN '31-60 Days'        WHEN CURRENT_DATE - t.duedate <= 90 THEN '61-90 Days'        ELSE '90+ Days'    END AS age_bucketFROM     Transaction tINNER JOIN     Customer c ON t.entity = c.idWHERE     t.type = 'CustInvc'    AND t.status = 'CustInvc:A'  -- Open    AND t.amountremaining > 0ORDER BY     days_overdue DESC

Sales Analysis#

Top Customers by Revenue:

SELECT     c.companyname,    COUNT(t.id) AS order_count,    SUM(t.amount) AS total_revenue,    AVG(t.amount) AS avg_order_value,    MAX(t.trandate) AS last_order_dateFROM     Customer cLEFT JOIN     Transaction t ON c.id = t.entity     AND t.type = 'CustInvc'    AND EXTRACT(YEAR FROM t.trandate) = 2025WHERE     c.isinactive = 'F'GROUP BY     c.id, c.companynameHAVING     SUM(t.amount) > 0ORDER BY     total_revenue DESCLIMIT 50

Inventory Analysis#

Inventory Valuation:

SELECT     i.itemid,    i.displayname,    i.quantityavailable,    i.averagecost,    i.quantityavailable * i.averagecost AS inventory_valueFROM     item iWHERE     i.isinactive = 'F'    AND i.quantityavailable > 0ORDER BY     inventory_value DESC