In NetSuite saved searches, you can combine `DECODE` and `CASE WHEN` to handle both simple equality checks and complex conditional logic within a single formula column. This approach is especially useful when:
* You need to treat different transaction types differently.
* Some transaction types require additional conditional checks (dates, statuses, quantities, etc.).
* You want to keep the formula compact but still handle complexity.
Generic Formula Pattern
sql
DECODE(
{type},
'TypeA', {simpleField}, -- Direct mapping for a transaction type
'TypeB', -- Transaction type needing more logic
(CASE
WHEN {dateField} < ({today} + 90)
AND {statusField} = 'Open'
THEN ({numericField1} - {numericField2})
* {rateField}
* (1 + ({taxRateField} / 100))
END),
0 -- Default if no match
)
How It Works
1. `DECODE` checks `{type}` first
* If it matches `”TypeA”`, a simple field value is returned (`{simpleField}`).
* If it matches `”TypeB”`, we drop into a nested `CASE WHEN` for further logic.
2. Nested `CASE WHEN` handles complex rules
* Date comparison (`{dateField} < ({today} + 90)`) — e.g., within the next 90 days.
* Status check (`{statusField} = ‘Open’`).
* Calculation: `(Quantity Difference) × Rate × (1 + Tax%)`.
3. `0` is returned for all other transaction types
* Acts as a default/fallback value.
When to Use This Approach
* Use `DECODE` for the initial transaction type branching — shorter and cleaner than multiple `CASE` blocks.
* Use `CASE WHEN` inside `DECODE` only for the types that need extra logic.
* This avoids repeating the same `{type}` comparison multiple times.
Benefits
Clear separation of simple vs. complex logic.
Reduces repetition for `{type}` checks.
Flexible — can handle both one-to-one mappings and calculated values.