Fix NetSuite Saved Search Invalid Expression and ORA-00904
NetSuite saved search Invalid Expression ORA-00904 invalid identifier formula
Also searched as
- Search error: Invalid Expression NetSuite formula
- ORA-00904 invalid identifier NetSuite saved search
- NetSuite formula numeric ORA-00932 inconsistent datatypes
- NetSuite saved search formula field not working
Short answer
Invalid Expression or ORA-00904 in a saved search means the formula references a column Oracle SQL cannot resolve, usually a wrong field id, a field from a join that is not in the search, or a record type that does not own the field. Other ORA codes such as ORA-00932 point to a data type mismatch.
Applies to: NetSuite saved searches, formula criteria, formula result columns and formula filters on all standard and custom record types
How to fix a saved search formula error
- 1Open the search and identify which formula caused it by removing one formula column or filter at a time until the error clears.
- 2Check field ids inside braces. Use the Field Help popup or the Records Catalog (Customization > Lists, Records, & Fields > Records Catalog) to confirm the id, for example {tranid} not {transactionnumber}.
- 3Confirm the field exists on the search record type. A formula on a Transaction search cannot use {itemid} without the join syntax {item.itemid}.
- 4Use the correct join syntax: {customer.companyname}, {item.cost}, {createdby.email}. The join must be a valid join for that search type.
- 5Match the formula type to the content. A Formula (Numeric) must return a number, a Formula (Text) a string, a Formula (Date) a date. Wrap values with TO_CHAR or TO_NUMBER when you mix them.
- 6Check parentheses, commas and string quotes. SQL strings use single quotes, so write CASE WHEN {status} = 'Open' THEN 1 ELSE 0 END.
- 7Handle nulls explicitly with NVL({amount},0) before arithmetic, and avoid dividing without a NULLIF guard.
- 8Save and preview with a small date range. Then add back removed formulas one by one.
What ORA-00904 really means in NetSuite
NetSuite translates a saved search into a SQL query against an Oracle based engine. Formula text between braces is mapped to columns before the query runs, and the rest of the expression is passed through as SQL. ORA-00904 invalid identifier appears when part of the expression is not a column or function the engine recognises.
Two distinct failures produce it. In the first, a brace reference is wrong, so NetSuite cannot map it. In the second, you used a word outside braces that is not a SQL function, for example a JavaScript style function like IF or a column name without braces. Oracle SQL uses CASE, DECODE, NVL, NVL2 and COALESCE instead.
Because the message does not name the failing token, bisect the formula. Replace the whole expression with a single field, confirm it works, then rebuild step by step.
CASE WHEN {status} = 'Pending Billing' AND {daysoverdue} > 30 THEN 'Late' ELSE 'OK' ENDNVL({amount},0) / NULLIF(NVL({quantity},0),0)Joins and field ids that look right but are not
Field ids in the Search UI labels differ from internal ids. The column label Document Number corresponds to {tranid}, and Item corresponds to {item}, which returns the item name, while {item.itemid} requires the join. Custom fields use ids such as {custbody_ship_priority}, and the script id must match exactly, including underscores and case.
Joins nest in a limited way. {customer.salesrep.email} is valid in many searches, but not all combinations are supported. If a nested join fails, add the intermediate value as a separate column and use the Summary or a second saved search instead.
Some fields are display only or not accessible to formulas on certain record types. Sublist fields on transactions, such as line level custom columns, are available as {custcol_xxx} on line level searches, but require Main Line set to false in the criteria to behave as expected.
Type mismatches: ORA-00932 and ORA-01722
Formula (Numeric) expects a number back. A CASE that returns text in one branch and a number in another triggers ORA-00932 inconsistent datatypes. Make every branch the same type, and convert explicitly with TO_CHAR({trandate},'YYYY-MM-DD') or TO_NUMBER({custbody_ref}) where needed.
ORA-01722 invalid number appears when TO_NUMBER hits a non numeric string, such as an empty custom text field or one holding dashes. Guard with CASE WHEN REGEXP_LIKE({custbody_ref},'^[0-9]+$') THEN TO_NUMBER({custbody_ref}) ELSE 0 END.
Date arithmetic is a frequent trap. Subtracting two dates returns a number of days in Oracle, but comparing a date to a string requires TO_DATE with the format used by the account, or better, the ADD_MONTHS and TRUNC functions with real date columns.
TRUNC({duedate}) - TRUNC({today})CASE WHEN REGEXP_LIKE({custbody_ref},'^[0-9]+$') THEN TO_NUMBER({custbody_ref}) ELSE 0 ENDWhen a formula is the wrong tool
Long nested formulas are hard to maintain and fail silently when a field is renamed. If you repeat the same CASE logic in many searches, store the result in a custom field populated by a User Event script or a workflow, and search on the field.
For complex joins that saved searches cannot express, SuiteQL through N/query or the SuiteAnalytics Workbook is more flexible and reports the failing SQL token more clearly.
Common pitfalls
- !Using field labels instead of internal ids inside braces.
- !Writing IF() or JavaScript syntax instead of CASE, DECODE or NVL2.
- !Mixing text and numeric branches in one CASE expression.
- !Referencing a joined field without the join prefix, or using an unsupported nested join.
- !Dividing by a field that can be zero or null without NULLIF.
- !Using double quotes for string literals instead of single quotes.
How an ERP-grounded AI assistant handles this
An assistant grounded in your NetSuite account can compare a formula's field ids to the real record schema, including your custom fields, point to the token that does not resolve, and suggest a corrected CASE with explicit type conversion. ERPray shows the query it builds and stays read-only, so you copy the corrected formula into the search after reviewing it.
Frequently asked questions
Does ORA-00904 mean my field does not exist?
Not always. It can also mean the field exists but not on the search record type, or that you used a word outside braces that Oracle SQL does not know. Test each brace reference alone in a simple column to find which part fails.
Why does my formula work in one search and not another?
Searches are built on different record types. A field id valid for a Transaction search may not exist in an Item search without a join. Check the field in the Records Catalog for the specific search type before reusing the formula.
Which functions can I use in NetSuite formulas?
Oracle SQL functions such as CASE, DECODE, NVL, NVL2, COALESCE, TRUNC, ROUND, TO_CHAR, TO_DATE, SUBSTR, INSTR, REGEXP_LIKE and aggregate functions in summary mode. NetSuite adds a few of its own, but SQL analytic functions are limited.
Why does the error only appear in summary searches?
In summary mode, formula columns must be grouped or wrapped in an aggregate. A raw field in an ungrouped formula can raise errors such as not a single-group group function. Set the Summary Type on the column or group by the field.
Related
How to use formula fields in a NetSuite saved search
In the saved search Results tab, add a column, set Field to "Formula (Text)", "Formula (Numeric)", "Formula (Date)" or "Formula (Currency)", then type an Oracle SQL expression into the Formula box using curly braces around field IDs, e.g. {trandate} or {item.custitem_weight}. Formula fields can also go on the Criteria tab so you can filter on the calculated value itself.
How-toHow to write and run SuiteQL queries in NetSuite
SuiteQL is NetSuite's read-only SQL dialect over the underlying record tables, run either interactively from Analytics > SuiteQL Query Tool (or the older /app/suiteanalytics query page), through REST at /services/rest/query/v1/suiteql, or programmatically via the N/query module in SuiteScript 2.x. It supports standard SELECT, JOIN, WHERE, GROUP BY and window functions against table names that mostly match record type IDs (transaction, transactionline, item, customer).
Error fixFix NetSuite INVALID_FLD_VALUE Error
INVALID_FLD_VALUE means NetSuite rejected a value you tried to set on a field because it does not match the field's expected type, list option, or reference record. Fix it by confirming the internal ID or text value actually exists on that field's source list and matches the field's value type (text versus list versus record reference) before setting it.
AdvancedN/query versus N/search: choosing the right SuiteScript data access module
N/query runs SuiteQL directly against NetSuite's relational schema and is generally cheaper and faster for joins, aggregation and large filtered pulls, while N/search wraps the same saved-search engine used by the UI and is better when you need formula columns, summary types or a saved search object you can reuse. Both consume governance units per page fetched, but N/query's ability to express a join in one query often beats the multiple round trips a search-based join pattern requires.
Error fixFix NetSuite SSS_USAGE_LIMIT_EXCEEDED Error
SSS_USAGE_LIMIT_EXCEEDED fires when a SuiteScript execution consumes all the governance units (usage points) allotted to its script type before it finishes. Fix it by checking runtime.getCurrentScript().getRemainingUsage() before expensive calls, yielding or rescheduling in Scheduled scripts, and moving heavy record-count work into Map/Reduce, which yields automatically across stages.
Error fixFix NetSuite SSS_MISSING_REQD_ARGUMENT Error
SSS_MISSING_REQD_ARGUMENT means a NetSuite API call, most often record.create(), record.load(), or search.create(), was called without a parameter that method requires, such as type or id. Fix it by checking the object literal you passed against the current SuiteScript 2.x API signature and confirming no required key is undefined at runtime.
AI for ERPAI for NetSuite, Beyond the Built-In Text Tools
NetSuite's built-in AI covers text generation, not grounded answers on your own data. See how a private LLM over SuiteQL adds real Q&A and controls.
Stuck on Oracle NetSuite?
Talk to engineers who work inside Oracle NetSuite every week, and who build private AI that answers these questions from your own ERP data.