SuiteQL is Oracle SQL wearing a NetSuite badge. Most of the time that’s fine. The rest of the time it fails in a way the error message doesn’t explain, or worse, it doesn’t fail at all and hands you a number that looks right.
Every query on this page was run against a live NetSuite account in September 2026. Where it says a query fails, we watched it fail. Where it says a query works, we watched it return rows.
Part one: it threw an error
The error strings are vague. “Invalid or unsupported search” covers at least five unrelated things below.
“Unknown identifier” on a field you’re sure exists
You wrote dateCreated on a transaction and SuiteQL says there’s no such field.
The transaction record carries several overlapping date fields. trandate is the transaction date. createddate and createddatetime are when the record was made. There’s lastmodifieddate as well. The REST API metadata labels createddate as “Date Created,” which is what sends people to the wrong one.
Worth knowing that this is record-specific rather than a global rule. The customer record does have a datecreated field. The transaction record doesn’t have dateCreated in any spelling.
Use trandate for the transaction date. And when a field name fails, pull the metadata for that record type and check the spelling instead of guessing a second time. There are three ways to do that in part three.
Same record, same trap: abbrevtype vs type vs recordtype, and shipdate vs trandate vs createddate.
Those last three are worth a second look, because they hold three different things for the same record. On an invoice, abbrevtype is INV, type is CustInvc, and recordtype is invoice. On an inventory adjustment they’re INV ADJT, InvAdjst and inventoryadjustment. If something you copied from a blog post filters on the wrong one of the three you get zero rows and no error.
“Unknown identifier” on a custom field you can see in the UI
Custom fields have two names. The label is what you see, “Lead Category.” The script ID is what SuiteQL takes, custentity_lead_category. Only the script ID works and you can’t work it out from the label.
sql
-- fails: Unknown identifier '"Lead Category"'
SELECT id, entityid, "Lead Category" FROM customer
-- works
SELECT id, entityid,
custentity_lead_category, -- raw id, e.g. 1
BUILTIN.DF(custentity_lead_category) AS label -- "Corporation"
FROM customer
WHERE custentity_lead_category IS NOT NULLA trap inside the trap. If you put the label in single quotes, SuiteQL doesn’t fail at all. It reads it as a string literal and hands you back a constant column, the same text on every row, named expr1.
sql
-- does NOT fail, and that's the problem
SELECT id, entityid, 'Lead Category' FROM customerThat returns a column full of the words “Lead Category” and nothing to tell you it isn’t your data.
To find the script ID: Customization > Lists, Records, and fields, (record) fields, open the field, read the ID at the top. The prefixes are custentity_ on entities, custitem_ on items, custbody_ on transactions, custcol_ on transaction lines, custrecord_ on custom records.
That’s one field at a time. The Records Catalog in part three lists every field ID on a record next to its label, which is faster when you don’t know what you’re looking for yet.
“Invalid or unsupported search” on a date comparison
sql
-- fails
WHERE trandate >= '2026-06-01'SuiteQL won’t quietly convert a quoted string to a date. The error names nothing near the actual problem, so if you’re new to this you’ll go hunting through your table and column names first.
sql
-- works
WHERE trandate >= TO_DATE('2026-06-01','YYYY-MM-DD')
-- also works, and nicer for rolling windows
WHERE trandate >= SYSDATE - 30The format mask isn’t optional and it isn’t forgiving. '06/01/2026' needs 'MM/DD/YYYY'. Get it wrong and you’ve traded one unhelpful error for another.
“Invalid or unsupported search” on string concatenation
It’s Oracle underneath, so concatenation is ||, not +. If you came from Snowflake or BigQuery or MySQL or SQL Server, + is the reflex and it fails every time.
sql
-- works
SELECT tranid || ' / ' || abbrevtype AS joined FROM transaction“Invalid or unsupported search” on ORDER BY with an aggregate
sql
-- fails: "Invalid or unsupported search"
SELECT type, COUNT(*) AS cnt FROM transaction GROUP BY type ORDER BY cnt DESC
-- also fails, differently: "An unexpected SuiteScript error has occurred"
SELECT type, COUNT(*) AS cnt FROM transaction GROUP BY type ORDER BY COUNT(*) DESCTwo different messages, neither of which tells you the real problem: SuiteQL won’t sort on an aggregate at the top level of a grouped query.
To be precise about it, because the next section depends on the distinction: it’s the aggregate SuiteQL objects to, not the alias. A plain alias sorts fine. ORDER BY cnt DESC fails because cnt is a COUNT(*).
Two fixes, both work.
sql
-- order by position
SELECT type, COUNT(*) AS cnt FROM transaction GROUP BY type ORDER BY 2 DESC
-- or wrap it so the count becomes an ordinary column
SELECT * FROM (
SELECT type, COUNT(*) AS cnt FROM transaction GROUP BY type
) ORDER BY cnt DESCI use the subquery version because it survives copy-paste edits without me having to recount positions.
An error when you GROUP BY the alias
sql
-- fails
SELECT TO_CHAR(trandate,'YYYY-MM') AS ym, COUNT(*) AS cnt
FROM transaction GROUP BY ymThe message is Unknown identifier 'ym'. Available identifiers are: {transaction=transaction}, which is unhelpful. It tells you the name doesn’t exist and then lists your tables, so the first place you go looking is the join.
Repeat the whole expression and it runs.
sql
-- works
SELECT TO_CHAR(trandate,'YYYY-MM') AS ym, COUNT(*) AS cnt
FROM transaction
GROUP BY TO_CHAR(trandate,'YYYY-MM')
ORDER BY ym DESCORDER BY ym is fine. GROUP BY ym isn’t. That looks arbitrary and it isn’t. WHERE, GROUP BY and HAVING all get evaluated before the SELECT list is built, so at that point the alias doesn’t exist yet. ORDER BY runs after, by which time ym is a real column. It’s one rule that lands differently depending on the clause. HAVING cnt > 1 fails with the same message for the same reason.
Worth understanding rather than memorizing, because it predicts the next one. Any clause that runs before the SELECT list won’t see your aliases.
If you’d rather write the expression once, wrap it. Inside the subquery the alias becomes an ordinary column, so the outer query can group on it.
sql
SELECT ym, COUNT(*) AS cnt
FROM (
SELECT TO_CHAR(trandate,'YYYY-MM') AS ym
FROM transaction
WHERE trandate >= TO_DATE('01/01/2025','MM/DD/YYYY')
)
GROUP BY ym
HAVING COUNT(*) > 1
ORDER BY ym DESCOne thing not to take from this: ORDER BY doesn’t always take the alias. It takes a plain one. It won’t take an aggregate, which is the section just above. ORDER BY cnt DESC fails on a grouped query even though ORDER BY ym DESC works. Two unrelated restrictions that both show up as “my alias broke it,” and that’s most of why this one takes so long to pin down.
The AI angle isn’t really the failing query, because that one errors loudly and you find out. It’s the fix. A model that has absorbed “aliases don’t work in SuiteQL” starts avoiding them everywhere and hands you ORDER BY 2 in queries where the alias was fine. Position numbers are the thing that quietly breaks the next time somebody adds a column to the SELECT list.
An error when BUILTIN.DF() and GROUP BY disagree
sql
-- fails: "Invalid or unsupported search"
SELECT BUILTIN.DF(type) AS type_label, COUNT(*) AS cnt
FROM transaction GROUP BY typeThe usual reading of this is that BUILTIN.DF() can’t be used with grouping. That isn’t it, and it’s worth correcting because the wrong diagnosis leads to the wrong fix. BUILTIN.DF() in the GROUP BY works perfectly fine.
What fails is selecting BUILTIN.DF(type) while grouping by bare type. Those are two different expressions, and SuiteQL wants the one you selected to be the one you grouped on. It’s ordinary grouping behavior wearing an unhelpful error message.
Both of these run.
sql
-- group by the same expression you selected
SELECT BUILTIN.DF(type) AS type_label, COUNT(*) AS cnt
FROM transaction
GROUP BY BUILTIN.DF(type)
ORDER BY 2 DESC
-- or aggregate on the raw column and resolve the label outside
SELECT BUILTIN.DF(type) AS type_label, cnt
FROM (
SELECT type, COUNT(*) AS cnt FROM transaction GROUP BY type
)
ORDER BY cnt DESCI use the second one. It keeps the grouping on an indexed integer column and does the label lookup once per group rather than once per row, and it reads better when the query grows.
Note that the failing example has two problems at once if you write it the way it usually gets written, with ORDER BY cnt DESC on the end. Fix the ORDER BY and it still fails, which is how people end up concluding BUILTIN.DF() is the culprit.
“Invalid search type” on inventorydetail
There’s no inventorydetail table. The error is blunt for once: Invalid search type: inventorydetail. The lines you see in the UI live in inventoryassignment. There’s a worked pattern in part three.
Part two: no error, wrong answer
These are the ones that cost you. Nothing fails, the number looks plausible, and it’s wrong. Through an AI connector they’re worse, because the answer arrives in confident prose with no SQL in front of you to check.
Your “most recent” rows are the oldest ones
sql
-- reads perfectly, returns rows from a decade ago
SELECT id, tranid, trandate FROM transaction
WHERE rownum <= 5
ORDER BY trandate DESCROWNUM gets assigned as rows are fetched, before ORDER BY runs. So this grabs five arbitrary rows and then sorts those five among themselves. The sort is real. It’s just sorting the wrong five.
On the account we tested this against, that query returned one row dated 2022 and four from 2012 and 2013. The correct version returned five rows from 2026. Same table, same instant, nine years apart.
sql
-- correct
SELECT id, tranid, trandate FROM transaction
ORDER BY trandate DESC
FETCH FIRST 5 ROWS ONLY
-- or sort inside, limit outside
SELECT id, tranid, trandate FROM (
SELECT id, tranid, trandate FROM transaction ORDER BY trandate DESC
) WHERE rownum <= 5This one matters more with an AI client than without, because the broken version is the one that reads most naturally. Ask for “the latest” or “the most recent” and that’s what tends to come back. If you see ROWNUM and ORDER BY in the same query, rewrite it with FETCH FIRST before you trust a row of it.
One aside if you’re writing tool instructions or a system prompt. FETCH FIRST works. Some published guidance for AI clients tells the model to avoid OFFSET/FETCH and use ROWNUM pagination instead, which steers it straight into this trap.
The same query returns different rows than it did last month
This one is specific to 2026.2 and it comes from Oracle’s release notes rather than from our testing.
If a SuiteQL query against generic transactions doesn’t specify a sort order, NetSuite applies a default. In 2026.2 that default changed:
“SuiteQL queries and Analytics datasets based on generic transactions use Transaction.tranDate as the default sort field when no sort order is specified.”
It used to be Transaction.tranDisplayName. Document name order, roughly alphabetical. Now it’s transaction date.
Nothing errors. Nothing warns you. Every unsorted query against transactions comes back in a different order than it used to, and so do the Analytics datasets built on them.
Most of the time that’s harmless, because most of the time you didn’t care about order. It stops being harmless the moment something downstream takes the first N rows.
-- which five you get is now a different five
SELECT id, tranid, trandate FROM transaction WHERE rownum <= 5That query was always arbitrary. It used to be consistently arbitrary, which is worse, because a consistent wrong answer is one you stop checking.
Three places to look. Any query without an ORDER BY where you use the first row or the first N. Scripts and integrations that read row one and assume they know what row one is. And Analytics datasets on generic transactions, which the release note names alongside SuiteQL.
The fix is the one you already know. Say what you mean.
ORDER BY trandate DESC
FETCH FIRST 5 ROWS ONLYNever rely on a default sort, in any database. It isn’t a contract. Here’s a vendor changing one in a point release, correctly documented, and it will still cost somebody a confusing week.
Every total is inflated, by roughly the number of lines per transaction
One transaction has a lot of rows in transactionline. The header line, one per item, plus tax, shipping, and the posting entries underneath. Join without filtering and every transaction multiplies into all of its lines.
On one account, about 16,000 invoices produced somewhere around 138,000 transaction lines. More than eight per invoice. A SUM across that unfiltered join is off and nothing warns you.
sql
-- one row per transaction
SELECT t.id, t.tranid, tl.amount
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type = 'CustInvc'
AND tl.mainline = 'T'Three things bite around this.
mainline is the string 'T' or 'F', not a boolean. mainline = true won’t do what you want.
When you actually want the item lines, quantity, item, rate, you want the opposite filter, mainline = 'F'. Don’t reflexively put mainline = 'T' on everything. Pick based on whether you’re after the header total or the line detail.
And if you’re summing line amounts to rebuild a header total, watch for taxline = 'T' and iscogs = 'T' rows or you’ll double-count tax and cost of goods. Note the spelling on that second one. There’s a taxline column but there is no cogs column, it’s iscogs, and getting it wrong is one of the few things in this section that errors rather than lying to you.
If a total looks too big by roughly the average number of lines per transaction, this is the first thing to check.
Your “not equals” filter dropped every NULL
sql
-- misses every customer with no territory
WHERE territory != 10
-- includes them
WHERE territory != 10 OR territory IS NULLStandard SQL behavior and still the easiest way to under-report. Inequality operators never match NULL.
Here’s the size of it on a real account. 17,985 customers. territory != 10 returns 9,711. Add OR territory IS NULL and it returns 17,251. The first version silently drops 7,540 rows, and 9,711 is a completely believable number to put in a report.
Applies to amounts and dates too, not just IDs.
One note on that example, because it’s a gotcha of its own: territory holds an internal ID, not a name. Comparing it to ‘East Region’ doesn’t return the wrong rows, it errors with “Invalid or unsupported search.” Most of the fields you’ll reach for here are IDs rather than labels. The Records Catalog in part three gives you the data type next to the field name, which is the quickest way to know before you write the filter. BUILTIN.DF() tells you the same thing from the other direction.
Your invoice list contains transactions that were voided
Zero amount doesn’t mean gone. Voided invoices, reversals and test entries all stay in the database.
sql
SELECT id, trandate, total, daysopen FROM transaction
WHERE abbrevtype = 'INV'
AND total != 0
AND voided = 'F'voided holds 'T' or 'F', single uppercase letter, same convention as mainline. Not a boolean, and not the word 'false'.
voided = 'false' does not error. It returns zero rows, every time, on any account. Which reads exactly like “you have no invoices” and is one of the more expensive things on this page to get wrong, because an empty result looks like an answer.
There’s also a separate void column alongside voided. They are not the same field. If you’re filtering on one, be sure you meant that one.
Your order count doesn’t match the GL
An INNER JOIN from a transaction to transactionline silently drops transactions that have no line rows.
sql
SELECT so.id, so.trandate, tl.item, tl.quantity, tl.amount
FROM transaction so
LEFT OUTER JOIN transactionline tl ON so.id = tl.transaction
WHERE so.type = 'SalesOrd'
AND so.trandate >= TO_DATE('2026-01-01','YYYY-MM-DD')The join column is tl.transaction and the item column is tl.item. There’s no parentId and no itemId on transactionline, and a query that uses either fails with “An unexpected SuiteScript error has occurred,” which is the least informative message SuiteQL produces. If you get that one, suspect a field name before you suspect anything else.
And when you’re filtering on the detail table in an outer join, put the filter in WHERE, not in ON. A filter in ON applies before the join and drops rows you meant to keep.
Status tells you now, not then
Transaction status is a current-state field. It doesn’t remember what it used to be.
Ask in June how many orders were pending fulfillment at the end of May and you’ll get today’s statuses filtered by a May date. Everything that shipped in June has already moved on, so it isn’t in your count. The number comes back looking clean.
Anything where you mean “as of some date in the past,” check whether the field you’re filtering actually holds history. Dates do. Statuses don’t.
Part three: patterns worth having
Resolve internal IDs into the labels you see in the UI
A lot of columns store an internal ID rather than a label. Query a customer’s status and you get 13. BUILTIN.DF(), display field, resolves it inline.
sql
SELECT id,
entitystatus, -- raw internal id, e.g. 13
BUILTIN.DF(entitystatus) AS status -- "Customer-Closed Won"
FROM customer
WHERE rownum <= 5Worth trying on anything that points at another record or a system list. Status, type, subsidiary, terms, category, territory, and your own custom list fields. It also makes AI-generated summaries noticeably better, because the model is reading a label instead of inventing a meaning for a number.
Remember the grouping rule above: if BUILTIN.DF(x) is in your SELECT and you’re grouping, group on BUILTIN.DF(x) too, or resolve it outside a grouped subquery.
Monthly trend in one query
sql
SELECT TO_CHAR(trandate,'YYYY-MM') AS ym, COUNT(*) AS cnt
FROM transaction
WHERE type = 'SalesOrd'
GROUP BY TO_CHAR(trandate,'YYYY-MM')
ORDER BY 1 DESC
FETCH FIRST 6 ROWS ONLYChange the mask for other buckets. 'YYYY' for annual, 'YYYY-MM-DD' for daily.
The current month is always partial. Hand that raw result to an assistant and it’ll describe a drop that’s just the calendar. Either exclude the current month or tell it that’s month-to-date.
This is calendar months, not fiscal periods. If your fiscal calendar doesn’t line up with the calendar year, this isn’t the query you want.
Item, warehouse and bin from inventory detail
The trap here is that there’s no inventorydetail table, and the warehouse isn’t on the detail row anyway.
Detail lines live in inventoryassignment. The whole table is id, transaction, transactionline, bin, inventorynumber, quantity, quantitystaged, pickcarton and packcarton. That’s it. No location column, and no status column either. The warehouse is on the parent line, transactionline.location. And the join key is the pair (transaction, transactionline), not one id.
sql
SELECT t.tranid AS build_number,
BUILTIN.DF(tl.item) AS item,
BUILTIN.DF(tl.location) AS warehouse,
BUILTIN.DF(ia.bin) AS bin,
ia.quantity
FROM transaction t
JOIN transactionline tl
ON tl.transaction = t.id
JOIN inventoryassignment ia
ON ia.transaction = t.id
AND ia.transactionline = tl.id
WHERE t.type = 'Build'
AND tl.location IS NOT NULL
AND t.tranid = 'YOUR_BUILD_NUMBER'For a work order use 'WorkOrd'.
The general version of this: when a field should exist and doesn’t, you’re usually looking one level too low. Line-level attributes, location, item, department, class, are on the transaction line. The detail rows underneath only carry what’s specific to the detail: bin, lot and serial number, quantity. Join up a level and it’s there.
A note on which table to query. transaction holds every transaction type, keyed by type, and going through it is the approach I’d recommend because one set of habits then works everywhere. But it isn’t true that the per-type tables don’t exist. salesorder and assemblybuild are both real and both queryable. They’re convenience views over the same data with a narrower column set, and the reason to skip them is consistency, not availability.
CTEs work, whatever you’ve been told
For years the answer was that SuiteQL has no WITH clause. It does. Oracle’s own SuiteQL documentation lists it, and we’ve run it.
sql
WITH monthly AS (
SELECT TO_CHAR(trandate,'YYYY-MM') AS ym, COUNT(*) AS cnt
FROM transaction
WHERE type = 'SalesOrd'
GROUP BY TO_CHAR(trandate,'YYYY-MM')
)
SELECT ym, cnt FROM monthly
WHERE cnt > 400
ORDER BY ym DESC
FETCH FIRST 5 ROWS ONLYOne documented limitation: you can’t reference a CTE from a WHERE clause the way you can a subquery.
This one goes past the syntax. If a tool description or a system prompt tells a model SuiteQL has no CTE support, it’ll avoid WITH and hand you a nest of inline subqueries instead, even where a readable CTE would have worked. The platform can do it. The instructions steer around it. With an AI layer in between, stale “the platform can’t do X” lore gets baked into what the assistant is willing to write. Same story as the FETCH FIRST note in part two, and it’s worth checking any guardrail document you’ve been handed against the platform rather than against its reputation.
Stop guessing field names
The thing that turns SuiteQL from frustrating into fast is refusing to guess. Three ways to ask, in order of who can reach them.
The Records Catalog
It answers most of part one of this page in one screen. tranDate is “Date” and createdDate is “Date Created,” sitting next to each other so you can see why people pick the wrong one. custbody_fwc_ic_dealer_value is “Dealer Value,” which is the label-versus-script-ID problem with both names visible. And the type column tells you cseg_custvertical is an INTEGER before you compare it to a string.
Setup > Records Catalog, or go straight to /app/recordscatalog/rcbrowser.nl in your account. Pick a record, open the Fields tab, and you get every field ID next to its label, its data type, and a Joins tab for what it connects to.
Two things to know about it. It needs the Records Catalog permission on your role. And it only shows record types your role can already see, so two people can open the same catalog and get different lists.
One caveat that matters here specifically. Oracle’s own SuiteQL documentation sends you to the Records Catalog to find record type and field names. The catalog’s own scope note says something narrower: “The Records Catalog is supported for the SuiteScript Analytic API and the REST Query API only. Therefore, the record types available in the Records Catalog and the record types accessible through the Connect Service may differ slightly.” So it’s the fastest field reference you have, and it isn’t the last word for SuiteQL. Use it to find the name, then confirm the one field you’re about to depend on.
ns_getSuiteQLMetadata
Through an MCP connector, on a record type, this gives you the real queryable fields and which ones are joinable. Same information, scoped to SuiteQL, available to the model rather than to you.
One row
SELECT * FROM inventoryassignment WHERE ROWNUM = 1Shows in one call that it has bin and no location, which was the whole puzzle above.
This is the one that’s always right, because it asks the thing you’re actually querying rather than a catalog of it. It found most of the errors in the previous version of this page and it takes about four seconds.
A model writes good SuiteQL once it has the real schema. Without it, it invents field names that look right.
Hit one that isn’t here? The first hour is free. Bring us the query that’s misbehaving and we’ll spend an hour on it with you, in your account.