NULLIF

Use NULLIF to selectively replace a specific value in your data with NULL. This can be helpful in situations where a particular value represents missing or invalid data, and you want to explicitly mark it as NULL for further analysis or processing.

Sample usage

Replace -1 in the Discountfield with NULL.

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:

NULLIF( Discount , -1)

This formula can be read, "If the Discountfield is -1, return null, otherwise return Discount."

You can then calculate the average discount ignoring orders with no discount:

AVG( Discount With Nulls )