NetSuite saved search CASE WHEN: multiple conditions, order and NULLs

The formula runs, every row gets a label, and some labels are wrong

Here is a stock label for an item saved search, written the way most people write it the first time:

CASE
  WHEN {quantityavailable} > 19 THEN 'In Stock'
  WHEN {quantityavailable} > 0 THEN 'Low'
  ELSE 'Out of Stock'
END

It saves. It runs. Every row gets a label. And some of the rows marked "Out of Stock" have no value in the field at all. Empty, not zero. The search gives no warning, and the column looks complete.

The cause is not NetSuite. NetSuite hands the formula to the Oracle database underneath, and Oracle's rules for CASE and NULL decide the result. Once you know those rules, CASE WHEN in a saved search is short and predictable. Until then it lies quietly.

A NULL comparison is never true, so it falls through to ELSE

NetSuite's SQL Expressions reference says formula expressions are evaluated by the Oracle database. In Oracle, a comparison against NULL is neither true nor false. It is UNKNOWN. The Oracle table of conditions containing nulls lists a = 10 with a NULL as UNKNOWN, and the same for a != 10.

A searched CASE only fires a branch when the condition is true. From the Oracle CASE reference: Oracle searches from left to right until it finds a condition that is true, returns that branch, and if none is true it returns the ELSE value. UNKNOWN is not true. So NULL > 19 fails, NULL > 0 fails, and the row lands in ELSE next to the items that really are at zero or below.

Inverting the test does not help. The same Oracle page on nulls notes that NOT FALSE is TRUE but NOT UNKNOWN is still UNKNOWN. WHEN NOT ({quantityavailable} > 0) misses the empty rows too.

Oracle's own NetSuite example handles this with an explicit branch. It is the sample in the SQL Expressions page:

CASE
  WHEN {quantityavailable} > 19 THEN 'In Stock'
  WHEN {quantityavailable} > 1 THEN 'Limited Availability'
  WHEN {quantityavailable} = 1 THEN 'The Last Piece'
  WHEN {quantityavailable} IS NULL THEN 'Discontinued'
  ELSE 'Out of Stock'
END

IS NULL is one of the few tests that returns TRUE for an empty value, so empty rows get their own label instead of borrowing someone else's.

Put the IS NULL branch first, every time

This is the one habit I would enforce in every saved search formula: when a field can be empty, the first WHEN tests for it.

The Oracle sample above puts IS NULL fourth, and it works, because no earlier branch can be true for an empty value. But the order only stays safe as long as nobody edits the ladder. Put IS NULL first and the rest of the branches only ever see real numbers. The ELSE then means exactly one thing.

CASE
  WHEN {quantityavailable} IS NULL THEN 'No quantity'
  WHEN {quantityavailable} > 19 THEN 'In Stock'
  WHEN {quantityavailable} > 0 THEN 'Low'
  ELSE 'Out of Stock'
END

The common shortcut is to wrap the field in NVL and move on. That fixes arithmetic, and NetSuite's support article on null values in formulas shows it for {quantityonhand} - {quantitycommitted}, which comes back blank when either side is null. It does not fix a CASE ladder. NVL({quantityavailable}, 0) > 0 is false for the empty rows, so they go to ELSE exactly as before, now merged with the zeros on purpose. We tested this against live data in NVL does not fix NULL in a CASE: the NVL version moved no rows at all. Use NVL when you want empty to mean zero. Use an IS NULL branch when you want to see them.

Multiple conditions go inside a single WHEN

There is no special syntax for several conditions. A WHEN takes a full condition, and conditions combine with AND and OR like anywhere else in Oracle SQL. NetSuite's syntax line for the searched form is:

CASE WHEN condition THEN return_expr [WHEN condition THEN return_expr]... [ELSE else_expr] END

So an item that is out of available stock but still has commitments against it can get its own label:

CASE
  WHEN {quantityavailable} IS NULL THEN 'No quantity'
  WHEN {quantityavailable} <= 0 AND {quantitycommitted} > 0 THEN 'Oversold'
  WHEN {quantityavailable} <= 0 THEN 'Out of Stock'
  WHEN {quantityavailable} > 19 OR {quantityonhand} > 100 THEN 'In Stock'
  ELSE 'Low'
END

Two things about that formula are easy to get wrong.

AND with a NULL is not FALSE. If {quantitycommitted} is empty on an item, {quantitycommitted} > 0 is UNKNOWN, the whole AND is not true, and the row moves on to the next branch. Here that is what you want. In other formulas it is the same silent fall-through as before, one level down. If the second field can be empty, test it with IS NULL or wrap it in NVL inside the condition.

Mixing AND and OR needs parentheses. Oracle's condition precedence table puts AND above OR, so A AND B OR C is read as (A AND B) OR C. If you meant A AND (B OR C), write the brackets. A saved search will not tell you which one you got, it will just show different rows.

The first true branch wins, so order is part of the logic

Oracle stops at the first condition that is true and never evaluates the rest. The CASE reference calls this short-circuit evaluation. The practical meaning is that overlapping ranges must go from narrow to wide.

In the Oracle sample, > 19 comes before > 1. Swap them and an item with 50 available matches > 1 first and is labelled "Limited Availability". Nothing errors. The 50 is simply never compared against 19.

The same applies to multiple conditions. In the Oversold example, <= 0 AND {quantitycommitted} > 0 must sit above the plain <= 0, otherwise the plain branch catches every oversold item before the specific one is checked. A useful habit: read each branch and ask which rows reach it, given that every branch above it has already taken its rows.

Every THEN must return the same type

The Oracle CASE reference states that for both simple and searched CASE, all return expressions must have the same data type, or all must be numeric. Text in one branch and a number in another is not allowed.

This matters most with ELSE. A ladder that returns labels and then ends in ELSE 0 breaks that rule. Return '0' as text, or return numbers in every branch.

It also has to agree with the formula type you picked. NetSuite's page on formulas in search results lists the result types: Currency, Date, Date/Time, Numeric, Percent, Text and HTML. Labels go in Formula (Text). Its own example is a CASE:

CASE WHEN {duedate} < CURRENT_DATE THEN 'Overdue' ELSE 'On Time' END

Formula (Text) results show as plain text. If a branch returns markup, the column needs Formula (HTML) instead.

In Criteria, return 1 or 0 and compare the number

A CASE in Results labels rows. A CASE in Criteria decides which rows exist. NetSuite's page on formulas in search criteria says only three formula types are available there, Formula (Date), Formula (Numeric) and Formula (Text), and records are filtered on the calculated value. Its example is a Formula (Numeric) of {today} - {startdate} with greater than or equal to 3.

For a rule with several conditions, the cleanest form is a numeric flag:

CASE
  WHEN {quantityavailable} IS NULL THEN 0
  WHEN {quantityavailable} <= 0 AND {quantitycommitted} > 0 THEN 1
  ELSE 0
END

Formula (Numeric), equal to 1. The rule lives in one place, each branch can be read on its own, and changing the filter means editing the CASE rather than rearranging criteria rows with parentheses and OR.

Why numeric and not text. A Formula (Text) criterion returning 'Y' and 'N' works, but text operators carry their own surprises. contains is a substring match, so a formula that returns ID numbers as text and filters with contains '2' matches every ID that has a 2 anywhere in it. We measured that in Formula (Text) contains is a substring match. A number compared with equal to has no such second meaning.

One more difference from the plain filters. NetSuite notes that {quantity} used in a Formula (Numeric) criterion includes negative values, while the standard Quantity filter leaves them out. A formula criterion sees the raw value, so your CASE has to decide what a negative means.

Criteria checks field names, Results does not

A misspelt field ID behaves differently depending on where the formula sits. In Criteria, an unresolved field token makes NetSuite refuse the search before it runs, as described in INVALID_FORMULA_FIELD in criteria. In Results, the same formula saves and each row shows an error string as if it were data, which we documented in ERROR: Field Not Found in a results formula.

So a long CASE that you are still writing is easier to debug in Criteria, where a bad field fails loudly, and then move to Results once it is clean. NetSuite's formula reference also asks for internal field IDs rather than display values in formulas, because display values can change with the user's language preference. A CASE comparing {status} = 'Pending Approval' is a translation away from matching nothing.

DECODE is shorter for equality, and it treats NULL differently

When every branch is an equality test on one field, the simple CASE form or DECODE says the same thing in less space. NetSuite's sample for the simple form:

CASE {state} WHEN 'NY' THEN 'New York' WHEN 'CA' THEN 'California' ELSE {state} END

The simple form compares with equals, so it inherits the NULL problem: an empty {state} never equals anything and goes to ELSE.

DECODE does not. The Oracle DECODE reference says that in DECODE, two nulls are considered equivalent, and if the expression is null, Oracle returns the result of the first search value that is also null. That gives an equality list with an explicit empty case:

DECODE({state}, NULL, 'No state', 'NY', 'New York', 'CA', 'California', 'Other')

DECODE cannot do ranges or AND/OR. For anything beyond a lookup table, stay with searched CASE.

Guard divisions inside a branch with NULLIF

A CASE branch that divides can fail on the rows where the divisor is zero. NetSuite's guidance on avoiding divide by zero errors is to write Y/NULLIF(X,0) instead of Y/X. NULLIF turns the zero into NULL, so the division returns NULL instead of an error. If those rows need a label of their own, put WHEN X = 0 above the branch that divides.

Where CASE stops: 1000 characters

Both the Criteria and Results pages give the same limit: 1000 characters per formula. Both add the same trap: the preview can work with a longer formula, and the search fails with invalid expression errors when you run it. A twelve-branch ladder with long field IDs and string labels reaches that limit sooner than you expect.

When a CASE grows that long, it is usually doing two jobs. Split it. Put the row filter in a Criteria formula and the labelling in a Results formula, and each one gets its own 1000 characters. Shorten labels to codes if the column is only consumed by an export. Remove branches that return the same value as ELSE, since they change nothing.

If you would rather describe the rule than type the ladder, MokuBot runs in a side panel inside your NetSuite session, under your own permissions, and writes the CASE from a plain-words description of what each row should get.

For the other ways saved search formulas fail quietly, from field IDs to the difference between Criteria, Results and Summary, see NetSuite saved search formulas: what breaks and why. The rule underneath most of them is the one this formula started with: a row that matches nothing does not disappear, it goes to ELSE.