Common SuiteQL errors, and what they actually mean

SuiteQL error messages are more helpful than NetSuite's reputation suggests — they are mostly Oracle errors passed through, plus a thin layer of NetSuite-specific rejections at the request level. Once you can tell those two categories apart, debugging gets quick. This is the reference table I keep open, followed by the six that account for most of the lost afternoons.

SuiteQL errors: the reference table

1. Unknown identifier

The single most common SuiteQL error, and it has one dominant cause: the analytics schema is not the saved search schema. They overlap heavily, which is exactly why people get caught. A field you have used in searches for years may be named differently, live on a different table, or not be exposed at all.

-- Fails: 'amountremaining' is search vocabulary, not an analytics column
SELECT t.tranid, t.amountremaining
FROM transaction t
WHERE t.type = 'CustInvc';

-- Works: the analytics column for the open amount
SELECT t.tranid, t.foreignamountunpaid
FROM transaction t
WHERE t.type = 'CustInvc';

The reliable move is to stop guessing. Open the Records Catalog browser in your account and read the columns and joins for the record you are querying — it is generated from your account, so it already accounts for the features you have enabled. Failing that, SELECT * FROM <table> WHERE ROWNUM <= 1 returns the column names as keys, which is a two-second answer for a custom record.

2. Ambiguous column

transaction, transactionline and transactionaccountingline all expose an id. The moment you join them without aliases, any bare id is ambiguous and the parse fails. This is a good failure: the alternative would be a query that silently picks the wrong one.

SELECT
    t.id            AS transaction_id,
    tl.id           AS line_id,
    tl.uniquekey,
    t.tranid,
    tl.quantity
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type = 'SalesOrd'
  AND tl.mainline = 'F'

Make the alias-and-prefix habit unconditional, including on single-table queries. It costs nothing and it means adding a join later never breaks the SELECT list.

3. GROUP BY errors, in both directions

Two distinct mistakes produce two different errors, and knowing which is which saves you reading the query three times.

• "Not a GROUP BY expression" — you selected a column that is neither aggregated nor grouped. The database cannot know which of the grouped rows' values you meant.
• "Group function is not allowed here" — you put an aggregate in WHERE. WHERE filters rows before grouping happens, so it cannot see a SUM(). That belongs in HAVING.

SELECT
    BUILTIN.DF(t.entity)            AS customer,
    COUNT(*)                        AS invoices,
    SUM(t.foreignamountunpaid)      AS open_balance
FROM transaction t
WHERE t.type = 'CustInvc'                    -- row-level filter
  AND t.foreignamountunpaid > 0
GROUP BY BUILTIN.DF(t.entity)
HAVING SUM(t.foreignamountunpaid) > 50000    -- group-level filter
ORDER BY 3 DESC

One more trap: you cannot reference a SELECT alias in the same SELECT list or in WHERE. A margin percentage that divides two summed aliases has to be computed in an outer query wrapped around the aggregate — there is a worked version in the SuiteQL transaction queries cookbook.

4. Type and date conversion failures

An "invalid number" error means you compared a numeric literal to something the database is holding as text. Free-text custom fields are the usual culprit: a field created as free-form text that happens to contain 4471 is still text, and custbody_ref = 4471 asks Oracle to convert every row's value to a number — one non-numeric value anywhere in the column and the whole query dies.

-- Fails if custbody_po_ref is a free-text field
WHERE t.custbody_po_ref = 4471

-- Works
WHERE t.custbody_po_ref = '4471'

Dates are the mirror image. Comparing a date column to '2026-06-30' forces an implicit conversion using the session's date format, which is a preference — so the query works for you in one account and fails, or worse succeeds with the wrong month, elsewhere. Always be explicit:

WHERE t.trandate >= TO_DATE('2026-06-01', 'YYYY-MM-DD')
  AND t.trandate <  TO_DATE('2026-07-01', 'YYYY-MM-DD')

For datetime columns such as lastmodifieddate, the half-open range also avoids the end-of-day bug: <= '2026-06-30' excludes everything stamped after midnight on the 30th. Period boundaries are easy to get subtly wrong by hand, so the fiscal period calculator will hand you the start and end dates for a given fiscal calendar. Timezone and posting-period subtleties are covered in SuiteQL date functions.

5. Apostrophes, and why you should parameterise

WHERE companyname = 'O'Brien Fasteners' is a syntax error, because the string ended at O. Doubling the apostrophe ('O''Brien Fasteners') works, but hand-escaping user input in a concatenated query string is how injection bugs get into ERP integrations.

const results = query.runSuiteQL({
    query: `SELECT id, entityid, companyname
            FROM customer
            WHERE companyname = ?
              AND isinactive = 'F'`,
    params: [userSuppliedName]
}).asMappedResults();

Placeholder count and params length must match, and a mismatch fails at parse time rather than doing something creative. That is the behaviour you want.

6. Request-level failures over REST

These are not SQL problems and no amount of rereading the query will fix them. The SuiteQL REST endpoint has requirements of its own: the statement goes in the JSON body as q, the content type must be JSON, and the request needs the Prefer: transient header. Miss the header and the request is rejected before your SQL is parsed.

• Reject before parse → headers or body shape. Prove it by running the identical SQL in a query tool.
• Authentication failure → OAuth signature, token, or the account-specific host in the URL. A sandbox refresh changes the account id in that host.
• Insufficient permission → the integration role. Feature availability and role permissions are separate settings, and both have to be right.
• Correct-looking response, wrong row count → paging. Check whether you received exactly the page size you asked for, which is the signature of a truncated set rather than a complete one.

The two failures that never raise an error

Worth repeating, because these produce numbers that get put in front of a CFO. First, permissions: SuiteQL runs as the calling role, so a restricted role legitimately sees fewer rows with no warning. A query validated as administrator can under-report by half in production. Second, truncation: the non-paged call returns up to a capped number of rows and does not flag that it stopped. Any total that comes back suspiciously round deserves a COUNT(*) check with identical filters.

Both are avoidable with two habits: count before you fetch, and test as the role. The rest of the discipline — bounding queries, keyset paging, keeping functions out of WHERE — is in SuiteQL performance tips. If you are still finding your feet with the schema, the SuiteQL tutorial for beginners is the shorter route. And once you have rows to hand over, the JSON to CSV converter turns a REST response into something a spreadsheet will open without complaining.

A debugging order that works

1. 1.Reproduce the SQL in a query tool. If it works there, the problem is the request or the role, not the query.
2. 2.Strip the query to SELECT COUNT(*) FROM one_table WHERE one_filter. Add clauses back one at a time until it breaks.
3. 3.Read the whole error, including any list of available identifiers.
4. 4.Check the field's type and existence in the Records Catalog before assuming the message is wrong.
5. 5.Rerun as the role that will run it in production, and compare row counts.

Working through that list is fifteen minutes when you know the schema and most of an afternoon when you do not. ERPray shortens the loop for the ad-hoc case: you ask the question in plain English, it generates the query against a dictionary built from your own account — real table and column names, your custom fields — runs it read-only, and shows the SuiteQL underneath. When the schema is derived from the account rather than guessed, the unknown-identifier error largely stops happening.