SuiteQL tutorial for beginners: your first eight queries

If you can write SELECT and WHERE, you can already write SuiteQL. The hard part of this SuiteQL tutorial is not the SQL. It is learning which tables exist, how transactions are split across two of them, and why a status column returns CustInvc:A instead of the word Open. Get those three things straight and the rest is ordinary querying.

SuiteQL

NetSuite's SQL-92-style query language, executed against the analytics tables and views that sit beneath the record model. It is Oracle-flavoured: the same functions, the same string and date handling, and no data-modifying statements. SuiteQL reads. It never writes.

The mental model: records above, tables below

Every NetSuite record you see in the UI has a table behind it. A customer record is a row in customer. An item is a row in item. A sales order is one row in transaction plus one row per line in transactionline. Custom records get their own table named exactly as you named the record — customrecord_equipment_audit — and custom fields become columns named after the field id: custentity_credit_tier, custbody_project_code, custrecord_inspection_date.

Where to run SuiteQL

1. 01

   Check your permissions first

   You need a role with the SuiteAnalytics Workbook permission to query at all, plus REST Web Services if you are going in over HTTP. Ask your administrator rather than guessing — the failure mode is an authorisation error that looks like a syntax problem. And note that SuiteQL respects role restrictions: two people running the same query can legitimately get different row counts.

2. 02

   Pick one of the three runners

   The REST query endpoint takes a POST to /services/rest/query/v1/suiteql with the header Prefer: transient and a JSON body of { "q": "SELECT ..." }. SuiteScript uses the N/query module: query.runSuiteQL({ query: sql }).asMappedResults(). A query tool SuiteApp installed into your account gives you a browser text box, which is the fastest way to learn. Community-maintained tools exist for this and are widely used; vet anything you install into production.

3. 03

   Run one line to prove the plumbing works

   Before debugging your logic, prove you can talk to the endpoint at all. SELECT COUNT(*) AS n FROM customer is enough. If that returns a number, everything after this is SQL, and SQL errors are readable.

4. 04

   Cap every exploratory query

   Add FETCH FIRST 50 ROWS ONLY, or WHERE ROWNUM <= 50 if your account rejects the newer syntax. This is a live production database shared with everyone who is trying to enter orders. An unbounded SELECT * on transactionline is felt.

5. 05

   Discover columns instead of guessing them

   Run SELECT * FROM customer FETCH FIRST 1 ROWS ONLY and read the keys that come back. That is your column list for this account, including every custom field, and it is more reliable than any documentation because it reflects your customisations.

6. 06

   Paginate anything real

   The REST endpoint takes limit and offset as query parameters, and returns links plus a hasMore flag. In SuiteScript, page the result set rather than materialising it. Any integration built on a single unpaginated call breaks the month your data grows.

Query 1: your first SELECT

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

Two things to notice. Booleans in NetSuite are the characters 'T' and 'F', not true and false — a WHERE isinactive = 0 returns nothing and no error. And entityid is the customer's name-or-number as configured in your account, while companyname is the company field; they differ in accounts using auto-numbered entity ids.

Query 2: counting and grouping

SELECT
    type,
    COUNT(*)            AS txn_count,
    SUM(foreigntotal)   AS total_amount
FROM transaction
WHERE trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
  AND trandate <  TO_DATE('2026-07-01', 'YYYY-MM-DD')
GROUP BY type
ORDER BY txn_count DESC

type returns internal codes: CustInvc for an invoice, SalesOrd for a sales order, VendBill for a vendor bill, Journal for a journal entry. Run this query once in your own account and you have the code list you will use for the rest of your career. The date filter uses a half-open range on purpose — BETWEEN on a column with a time component quietly drops the last day.

Query 3: turning ids into words

SELECT
    tranid                  AS invoice_no,
    trandate,
    BUILTIN.DF(entity)      AS customer,
    BUILTIN.DF(status)      AS status_text,
    foreigntotal            AS invoice_total
FROM transaction
WHERE type   = 'CustInvc'
  AND status = 'CustInvc:A'
ORDER BY trandate DESC
FETCH FIRST 100 ROWS ONLY

BUILTIN.DF() returns the display value of a list or record field — the customer's name rather than internal id 4821. Status codes are namespaced by transaction type, so an open invoice is CustInvc:A and the same letter means something different on a sales order. Always pair a status filter with a type filter.

Query 4: your first join

SELECT
    t.tranid                    AS order_no,
    tl.linesequencenumber       AS line_no,
    BUILTIN.DF(tl.item)         AS item_name,
    tl.quantity,
    tl.rate,
    tl.netamount
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type  = 'SalesOrd'
  AND t.tranid = 'SO10482'
  AND tl.mainline = 'F'
ORDER BY tl.linesequencenumber

tl.mainline = 'F' is the single most important filter in NetSuite querying. Every transaction has a main line that carries the header total, plus real item lines, plus tax and shipping lines. Forget the filter and every total you compute is roughly double. Deeper join patterns, including left joins and custom records, are covered in SuiteQL join examples.

Query 5: aggregating across a join

SELECT
    BUILTIN.DF(t.entity)        AS customer,
    COUNT(DISTINCT t.id)        AS invoices,
    SUM(tl.netamount)           AS invoiced_revenue
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type = 'CustInvc'
  AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
  AND tl.mainline = 'F'
  AND tl.taxline  = 'F'
GROUP BY BUILTIN.DF(t.entity)
HAVING SUM(tl.netamount) > 25000
ORDER BY invoiced_revenue DESC

COUNT(DISTINCT t.id) rather than COUNT(*), because joining to lines multiplies the header. HAVING filters after grouping, which is the distinction a saved search hides behind two different tabs. This one query replaces a summary saved search and a spreadsheet.

Query 6: a left join, and what it reveals

SELECT
    c.entityid,
    c.companyname,
    c.email
FROM customer c
LEFT JOIN transaction t
       ON t.entity = c.id
      AND t.type   = 'CustInvc'
      AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
WHERE c.isinactive = 'F'
  AND t.id IS NULL
ORDER BY c.companyname

The date and type conditions sit in the ON clause, not the WHERE. Move them to WHERE and the left join collapses into an inner join, returning nothing. That single distinction accounts for a surprising share of wrong SuiteQL answers.

Query 7: dates that mean something to finance

SELECT
    ap.periodname,
    ap.startdate,
    SUM(tal.amount)     AS net_posted
FROM transactionaccountingline tal
JOIN transaction      t  ON t.id  = tal.transaction
JOIN accountingperiod ap ON ap.id = t.postingperiod
WHERE t.posting   = 'T'
  AND ap.isadjust = 'F'
  AND ap.startdate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -6)
GROUP BY ap.periodname, ap.startdate
ORDER BY ap.startdate

postingperiod, not trandate. An invoice dated 31 January and entered in February posts to February, and finance cares about the period. ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -6) is the first day of the month six months ago, which gives you a rolling window that never needs editing. The full set of date functions and their traps is in SuiteQL date functions.

Query 8: a custom record and a custom field

SELECT
    r.id,
    r.name                              AS inspection_ref,
    r.custrecord_inspection_date        AS inspected_on,
    c.companyname,
    c.custentity_credit_tier            AS credit_tier
FROM customrecord_equipment_audit r
JOIN customer c ON c.id = r.custrecord_audit_customer
WHERE r.custrecord_inspection_date >= TO_DATE('2026-04-01', 'YYYY-MM-DD')
  AND c.custentity_credit_tier IN ('A', 'B')
ORDER BY r.custrecord_inspection_date DESC

Substitute your own record and field ids — the shape is what transfers. Custom record tables expose id, name, created, lastmodified and isinactive alongside your custrecord_* columns. Find the ids under Customization > Lists, Records & Fields rather than guessing from the label, because the label and the id drift apart over time.

Four mistakes everyone makes once

• Forgetting `mainline = 'F'`. Totals roughly double. This is the first thing to check whenever a number looks about twice as large as it should be.
• Comparing a timestamped column with `=`. createddate carries a time, so equality against a date matches only midnight. Use TRUNC() or a half-open range.
• Filtering status without type. Status codes are namespaced per transaction type, so a bare status filter either returns nothing or returns the wrong documents.
• Assuming a column exists. Availability varies by feature set and customisation. SELECT * on one row tells you the truth for your account in two seconds.

Where this goes next

Eight queries in, you can already answer things a saved search cannot: multi-level aggregates, anti-joins, self-joins and arbitrary date maths. What you have also taken on is responsibility for the definitions — nothing stops you double-counting tax lines or including non-posting transactions. That trade-off, and the migration path from an existing search library, is laid out in SuiteQL vs saved search.

Two practical helpers for the boring parts: results land as JSON from the REST endpoint and usually need to become a spreadsheet, which the JSON to CSV converter does without pasting your data into a random website, and period boundaries for fiscal filters can be worked out with the fiscal period calculator.

The reason ERPray shows the SuiteQL behind every answer is exactly this tutorial's point. A number without its query is a claim. A number with the query underneath is something you can check — the mainline filter, the posting flag, the period range — in about five seconds.