SuiteQL Answers
Short answers to specific SuiteQL questions: what an error message actually means, which syntax the dialect accepts, and which queries return a wrong answer without erroring. Every page traces to a probe run against one live NetSuite 2026.1 account on the Administrator role, through N/query.runSuiteQL and, where a page says so, through the REST endpoint. Where a finding rests on a narrow test, the page says so too.
The probes were driven from MokuBot, the NetSuite agent we build, which is the only reason a couple of hundred of them could be run and re-run in an afternoon. What they found are properties of SuiteQL, not of the tool that sent them.
Error messages, decoded6
- SuiteQL says syntax error near FETCH, but my FETCH FIRST clause is fine. What is wrong?Your FETCH clause is probably fine. A column that does not exist on the table is the usual cause. Delete the FETCH FIRST clause, run the statement again, and the second error names the real problem.
- What does SuiteQL mean by Unknown identifier 'x'. Available identifiers are: {transaction=transaction}?The column does not exist on that table. Despite the wording, "Available identifiers" lists the table aliases in scope, not the columns you could have used. Adding a FETCH FIRST clause can hide this error behind a syntax error.
- What does the SuiteQL error 'Invalid or unsupported search' mean?A generic refusal carrying no error code, no position and no identifier name. In this run it came from CAST(id AS VARCHAR(20)), a date column compared to a bare ISO string, GROUPING SETS, and one RIGHT JOIN.
- What does 'N/query.runSuiteQL error: Unexpected Error' mean in SuiteQL?It carries no code, no position and no identifier name. Read it as "something here is unsupported" and bisect the query. In this run it came from three BUILTIN calls and from a column that does not exist.
- How do I list the tables and columns in a NetSuite account from SuiteQL?You cannot. OA_TABLES and OA_COLUMNS are rejected with "Invalid search type", and ALL_TABLES, USER_TAB_COLUMNS and ALL_TAB_COLUMNS are absent too. For columns, run SELECT * with FETCH FIRST 1 ROWS ONLY; for custom records, query customrecordtype.
- Why does CONNECT BY fail in SuiteQL with no_root_node?START WITH CONNECT BY PRIOR fails at parse time here: a syntax error at the column inside START WITH, plus no_root_node. The statement does not reach execution, so LEVEL and SYS_CONNECT_BY_PATH stay untested. Build the tree yourself.
Row limits and pagination3
- Why does LIMIT 5 not work in SuiteQL, and what should I use instead?LIMIT is not in the SuiteQL dialect. Both engines reject it with a parse error pointing at the number after it. Use FETCH FIRST 5 ROWS ONLY; WHERE ROWNUM <= 5 and SELECT TOP 5 also worked.
- Why does SuiteQL OFFSET return the same rows as page 1?The SQL clause OFFSET n ROWS FETCH NEXT m ROWS ONLY has its OFFSET silently discarded on both engines, even with an explicit ORDER BY. Every page returns the first rows again. Paginate with the REST ?offset= parameter instead.
- Why does totalResults in the SuiteQL REST response not match SELECT COUNT(*)?totalResults is not a row count. It reports min(rows your query returns, limit x 1000), so on a larger result set it reports the cap and calls it the total, HTTP 200, no flag. Run SELECT COUNT(*) separately.
Dates and time3
- Why do SYSDATE and CURRENT_DATE return different times in the same SuiteQL query?Two different clocks. SYSDATE follows NetSuite's server clock, which read UTC-07:00 in this run; CURRENT_DATE and CURRENT_TIMESTAMP follow the calling session's timezone. They came back ten hours apart in the same row, with no error.
- Why does my SuiteQL date come back as 8/20/2026 with no time?Date values serialise as M/D/YYYY in this run and any time component is dropped. The time is still in the column: select TO_CHAR(col,'YYYY-MM-DD HH24:MI:SS'). A TO_DATE mask parses input, it does not control output.
- How do I compare a date column to a date literal in SuiteQL?TO_DATE('2026-01-01','YYYY-MM-DD'), DATE '2026-01-01' and the bare string '1/1/2026' all work and returned identical counts. The bare ISO string '2026-01-01' fails with "Invalid or unsupported search". BETWEEN with TO_DATE on both sides works.
Joins and reconciliation4
- Why does my SuiteQL RIGHT JOIN between transaction and transactionLine return 0 rows?RIGHT JOIN between transaction and transactionLine runs as an inner join and returns zero rows. FULL OUTER is downgraded to a LEFT join from the header. No error. LEFT JOIN from the header and Oracle (+) both work.
- Why does my transactionAccountingLine join return far more rows than expected?You joined on transaction alone. transactionAccountingLine must match transactionLine on both transaction and line id. The transaction-only join matches every accounting line against every line of the same document, silently multiplying rows and any sum built on them.
- Why can't I select subsidiary from transactionAccountingLine in SuiteQL?There is no subsidiary column on transactionAccountingLine, and selecting it fails with a bare "Unexpected Error" that never names the column. Join transactionLine on both transaction and line id, then read subsidiary from there.
- How do I filter SuiteQL records by a multi-select employee field?Do not filter the displayed multi-select column as a scalar value. Filter the generated MAP_ relationship table, then use its record IDs in the main query.
What comes back3
- Does BUILTIN.DF silently drop rows in SuiteQL?No. Wrapping a column in BUILTIN.DF returned the same row count as the bare table in every form tested. The gap people see is COUNT(BUILTIN.DF(col)) skipping nulls, which is ordinary COUNT(expr) behaviour, not a row drop.
- Why does SuiteQL return CustInvc instead of Invoice for transaction type?The type column holds internal codes: CustInvc, VendBill, ExpRept, CuTrSale, RevArrng and so on. A filter written against the display name Invoice matches no row. Use the code, or filter on BUILTIN.DF(type), which selected the same rows.
- Why is my quoted column alias lowercased in SuiteQL results?Returned JSON keys come back lowercased on both engines even when the alias is double-quoted. SELECT id AS "Transaction ID" returns the key "transaction id": the space survives, the capitals do not, so row["Transaction ID"] is undefined.