Handling Null Parameters and Results in Saved Search

By design, Saved Search uses SQL statements. In SQL, when one of the parameters or variables in an expression or formula is null then by design the result is also null. To illustrate, assume you have an expression:

        {quantityonhand} – {quantitycommitted}

When either {quantityonhand} or {quantitycommitted} is blank on the Item Record, then the results will show blank. If {quantityonhand} is 90 while {quantitycommitted} is null/blank, then result will show blank/null even if the expected result is 90.

To resolve, users may apply the NVL(expr1, expr2) function in the variables. This function looks at the first expression expr1 and replaces it with the second expression expr2 if it is blank. Therefore, the more sound statement on this scenario is:

        NVL({quanityonhand}, 0) – NVL({quantitycommitted},0)