Writing SuiteQL that performs

SuiteQL often fails silently. A wrong filter returns zero rows or every row, and NetSuite raises no error. This page covers the patterns that keep a report correct and quick to refresh, starting with how the app runs your statement.

#How the app runs your statement

Apart from the wizard's preview, your SQL never runs while someone waits on a page. The refresh Map/Reduce runs it in the background. Each pass uses one of these forms:

So your statement usually runs inside a derived table, and the app reads it in windows. The sections below show how to write for both.

#Transaction status: write both forms

SuiteQL returns transaction.status as a bare letter, such as B. The form it matches in a filter depends on context:

• In a top-level WHERE, only the prefixed form matches: 'SalesOrd:B'.
• Inside a derived table, only the bare form matches: 'B'.

Because the app wraps your statement to count it and to page it, a filter that uses only one form can work in one pass and match nothing in another. List both forms, and restrict the transaction type so that the bare letters cannot match another type:

WHERE t.type = 'SalesOrd'
  AND t.status IN ('SalesOrd:F', 'SalesOrd:G', 'F', 'G')

The letters mean different things for each transaction type. C is Cancelled on a sales order but Rejected by Supervisor on a purchase order. To check the meanings in your account, select BUILTIN.DF(t.status) next to t.status. With NOT IN, the wrong form excludes nothing and gives no visible sign, so check a count against a known figure.

#Display values with BUILTIN.DF

BUILTIN.DF(column) returns a field's display value, such as a customer name, a status label or a class name, without a join. Use it for any list or record field that viewers read.

When the value should also link to its record, select the internal id beside it and hide that column in the wizard:

SELECT
    t.id AS transaction__id,
    t.tranid AS document_number,
    t.entity AS customer__id,
    BUILTIN.DF(t.entity) AS customer,
    TO_CHAR(t.trandate, 'YYYY-MM') AS month,
    t.foreigntotal AS amount
FROM transaction t
WHERE t.type = 'SalesOrd'
  AND t.status IN ('SalesOrd:F', 'SalesOrd:G', 'F', 'G')
  AND t.trandate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -36)
ORDER BY t.id

#Columns and aliases

• Give every column a unique alias. The preview wraps your statement, so it rejects duplicate names.
• Column keys are lower case. NetSuite lowercases every result column name, so AS "Transaction Date" becomes the key transaction date. Quoted aliases with spaces work. Plain aliases such as transaction_date, with a label set in the Columns step, are the simplest choice.
• Select months as text. TO_CHAR(t.trandate, 'YYYY-MM') AS month, with the month format, gives month filters, the grain switch and drill-through by month.
• Aggregate dates before you format them. MAX(TO_CHAR(...)) compares strings. Use TO_CHAR(MAX(...), ...) instead.

#Bound the window in the SQL

A report takes no runtime parameters, so put the date range in the query itself. ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -36) keeps a rolling three years that moves with the calendar. Viewers can narrow further with filters, but nothing can widen a range the SQL excludes.

#Paging needs a stable order

The app reads a result larger than 5,000 rows in windows, and each window must line up with the next. End the statement with an ORDER BY on a unique column or set of columns, such as t.id or t.id, tl.linesequencenumber. Without a unique order, NetSuite does not guarantee the same row order on each read.

The refresh also checks that each window moves forward and stops if one repeats, because some statements ignore OFFSET in NetSuite.

#Partial refresh

For a large result where few rows change between runs, set Refresh mode to Partial on the Details step, then choose two columns:

• Unique identifier column: one value per row, such as an internal id. For line-level rows, build one with t.id || '-' || tl.id AS line_key.
• Last modified column: a date or datetime column, such as t.lastmodifieddate, that changes every time the row changes. Only date and datetime columns are offered.

Each run reads only rows whose last-modified value is later than the stored watermark, minus a safety overlap of 60 minutes by default. It then merges them by key into the stored result. Rows deleted in NetSuite stay until someone runs Full Refresh. A change set too large for one execution falls back to a full run.

#Before you publish

1. Preview, then open the report and compare its row count with a count you trust.
2. If the query filters on status, read the count after the first refresh, not only the preview.
3. Check one figure by hand against NetSuite.

Related: The report wizard step by step · Refresh and scheduling · Drill-through · Filters and the grain switch