Setting Up the SuiteQL Query Tool in NetSuite

The SuiteQL Query Tool is a built-in NetSuite interface for running SQL-style queries directly against your NetSuite data. It replaces the older ODBC approach for ad-hoc data exploration and is far more flexible than saved searches for complex data retrieval.

Accessing the Query Tool

Navigate to Analytics > SuiteQL Query Tool in the NetSuite menu. If you don't see it, check that SuiteAnalytics Workbook is enabled in your account and that your role has the SuiteAnalytics Workbook permission.

Role requirements:

• The role needs the SuiteAnalytics Workbook permission
• For viewing all record types, the role needs relevant record-level permissions
• Administrator and full-access roles have this by default

Your First Query

The query tool provides a text area for entering SuiteQL and a results grid below. Start with a simple query:

SELECT
  id,
  companyname,
  email,
  datecreated
FROM customer
WHERE isinactive = 'F'
ORDER BY datecreated DESC
FETCH FIRST 25 ROWS ONLY

Click Run (or press Ctrl+Enter). Results display in a grid below the query editor. You can export results to CSV directly from the interface.

Key syntax differences from standard SQL:

• Use FETCH FIRST N ROWS ONLY instead of LIMIT (SuiteQL follows Oracle SQL conventions)
• String comparisons use 'T' and 'F' for booleans
• Date fields return as strings in MM/DD/YYYY format by default
• Use BUILTIN.DF() to get display values for list/record fields

Exploring Available Tables

SuiteQL exposes NetSuite records as database tables. The main ones:

Transaction tables:

-- All transactions (invoices, payments, bills, etc.)
SELECT * FROM transaction FETCH FIRST 1 ROWS ONLY
 
-- Transaction lines
SELECT * FROM transactionline FETCH FIRST 1 ROWS ONLY

Entity tables:

-- Customers, vendors, employees
SELECT * FROM customer FETCH FIRST 1 ROWS ONLY
SELECT * FROM vendor FETCH FIRST 1 ROWS ONLY
SELECT * FROM employee FETCH FIRST 1 ROWS ONLY

Item tables:

SELECT * FROM item FETCH FIRST 1 ROWS ONLY

To discover columns on any table, run a SELECT * with a single row fetch. The column headers show you what's available.

The Record Catalog

For a complete list of queryable tables:

SELECT * FROM metadata.tables ORDER BY name

And for columns on a specific table:

SELECT * FROM metadata.columns WHERE table_name = 'transaction' ORDER BY name

This metadata approach is more reliable than guessing column names.

Common Query Patterns

Joining transactions with lines

Most financial reporting queries join the transaction header with its lines:

SELECT
  t.tranid AS document_number,
  t.trandate,
  BUILTIN.DF(t.type) AS transaction_type,
  BUILTIN.DF(t.entity) AS customer_name,
  BUILTIN.DF(tl.item) AS item_name,
  tl.quantity,
  tl.rate,
  tl.netamount
FROM transaction t
INNER JOIN transactionline tl ON t.id = tl.transaction
WHERE t.type = 'SalesOrd'
  AND t.trandate >= TO_DATE('01/01/2026', 'MM/DD/YYYY')
ORDER BY t.trandate DESC
FETCH FIRST 100 ROWS ONLY

Aggregation queries

SELECT
  BUILTIN.DF(tl.item) AS item_name,
  SUM(tl.quantity) AS total_qty_sold,
  SUM(tl.netamount) AS total_revenue,
  COUNT(DISTINCT t.id) AS order_count
FROM transaction t
INNER JOIN transactionline tl ON t.id = tl.transaction
WHERE t.type = 'SalesOrd'
  AND t.trandate >= TO_DATE('01/01/2026', 'MM/DD/YYYY')
  AND tl.mainline = 'F'
  AND tl.item IS NOT NULL
GROUP BY BUILTIN.DF(tl.item)
ORDER BY total_revenue DESC
FETCH FIRST 20 ROWS ONLY

Custom record queries

Custom records are accessible by their internal ID:

-- Replace 'customrecord_your_record' with actual ID
SELECT * FROM customrecord_your_record FETCH FIRST 10 ROWS ONLY

Using BUILTIN Functions

SuiteQL provides built-in functions that handle NetSuite-specific data types:

BUILTIN.DF(field) — Display Function. Returns the text display value instead of the internal ID for list/record fields:

-- Without DF: returns internal ID like 5
SELECT entity FROM transaction FETCH FIRST 1 ROWS ONLY
 
-- With DF: returns 'Acme Corporation'
SELECT BUILTIN.DF(entity) FROM transaction FETCH FIRST 1 ROWS ONLY

BUILTIN.CF(field, type) — Consolidation Function. Used for currency consolidation in multi-subsidiary environments.

Saving and Reusing Queries

The query tool doesn't have a native "save" feature in the UI. Workarounds:

1. SuiteScript N/query module — save queries as scripts that can be triggered on schedule or via Suitelet
2. SuiteAnalytics Workbook — use SuiteQL as the data source for workbook datasets
3. External documentation — maintain a library of useful queries in your team wiki or version control

For queries you run frequently, building them into a SuiteAnalytics Workbook or a scheduled SuiteScript is more maintainable than re-typing them.

Performance Tips

• Always use FETCH FIRST N ROWS ONLY during development to avoid long-running queries
• Filter on indexed fields (id, trandate, type) in WHERE clauses
• Avoid SELECT * in production queries — specify only the columns you need
• Use INNER JOIN instead of subqueries when possible
• Break complex queries into CTEs (Common Table Expressions) for readability:

WITH recent_orders AS (
  SELECT id, entity, trandate, total
  FROM transaction
  WHERE type = 'SalesOrd'
    AND trandate >= TO_DATE('01/01/2026', 'MM/DD/YYYY')
)
SELECT
  BUILTIN.DF(entity) AS customer,
  COUNT(*) AS order_count,
  SUM(total) AS total_value
FROM recent_orders
GROUP BY BUILTIN.DF(entity)
ORDER BY total_value DESC
FETCH FIRST 10 ROWS ONLY

Troubleshooting

"Field not found" errors: Column names in SuiteQL don't always match the UI label. Use the metadata catalog query to find the correct column name.

Permission errors: Your role needs access to both the SuiteQL tool and the underlying record types. A role that can't view vendor records won't be able to query the vendor table.

Slow queries: Queries without proper filtering can scan millions of rows. Always include date ranges and type filters on the transaction table.

NULL handling: Use NVL(field, default) or COALESCE(field1, field2, default) to handle NULLs in calculations.