# Fix NetSuite Saved Search Invalid Expression and ORA-00904 > Source: https://netray.co/erp-help/netsuite-saved-search-invalid-expression-ora-00904-formula > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## 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. ## Kind error ## System netsuite ## Erp Oracle NetSuite ## Module Saved Searches / Formula Errors ## Query NetSuite saved search Invalid Expression ORA-00904 invalid identifier formula ## Alternate Queries - 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 ## Applies To NetSuite saved searches, formula criteria, formula result columns and formula filters on all standard and custom record types ## Steps ### How to fix a saved search formula error **Items:** - Open the search and identify which formula caused it by removing one formula column or filter at a time until the error clears. - Check 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}. - Confirm the field exists on the search record type. A formula on a Transaction search cannot use {itemid} without the join syntax {item.itemid}. - Use the correct join syntax: {customer.companyname}, {item.cost}, {createdby.email}. The join must be a valid join for that search type. - Match 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. - Check parentheses, commas and string quotes. SQL strings use single quotes, so write CASE WHEN {status} = 'Open' THEN 1 ELSE 0 END. - Handle nulls explicitly with NVL({amount},0) before arithmetic, and avoid dividing without a NULLIF guard. - Save and preview with a small date range. Then add back removed formulas one by one. ## Sections ### What ORA-00904 really means in NetSuite **Body:** 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. **Code:** - CASE WHEN {status} = 'Pending Billing' AND {daysoverdue} > 30 THEN 'Late' ELSE 'OK' END - NVL({amount},0) / NULLIF(NVL({quantity},0),0) ### Joins and field ids that look right but are not **Body:** 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 **Body:** 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. **Code:** - TRUNC({duedate}) - TRUNC({today}) - CASE WHEN REGEXP_LIKE({custbody_ref},'^[0-9]+$') THEN TO_NUMBER({custbody_ref}) ELSE 0 END ### When a formula is the wrong tool **Body:** 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. ## 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. ## Ai Angle 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. ## Faq ### Does ORA-00904 mean my field does not exist? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms NetSuite saved search invalid expression, ORA-00904 NetSuite, NetSuite formula error, ORA-00932 NetSuite formula, ORA-01722 invalid number NetSuite, NetSuite CASE WHEN formula, saved search formula join syntax, NetSuite formula numeric text date --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json