Sample usage
NULLIF(Discount, -1)
Syntax
NULLIF(input_expression, expression_to_match)
Parameters
input_expression
The expression to evaluate. You can use any valid expression as the input_expression
.
expression_to_match
NULLIF compares expression_to_match
to input_expression
. If the two are equal, NULLIF returns null, otherwise it returns the input_expression
. You can use any valid expression as the expression_to_match
.
Example
Suppose you want to calculate the average discount given to your customers. Your application represents "no discount" as -1. The formula AVG(Discount)
will count -1 and return an incorrect result. To avoid this, you can convert those -1s to nulls. To do this, create a new field called, for example, Discount With Nulls:
This formula can be read, "If the Discount field is -1, return null, otherwise return Discount."
You can then calculate the average discount ignoring orders with no discount: