NULLIF and CASE expressions

Use the CASE and NULLIF expressions for conditional expressions in Derby.

NULLIF expression

Syntax

NULLIF(L, R)

CASE expression

You can place a CASE expression anywhere an expression is allowed. It chooses an expression to evaluate based on a boolean test.

Syntax

CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END

ThenExpression and ElseExpression are both expressions that must be type-compatible. For built-in types, this means that the types must be the same or a built-in broadening conversion must exist between the types.

You do not need to use the CASE expression for avoiding NullPointerExceptions when a nullable column becomes a method receiver.

-- returns 3
VALUES CASE WHEN 1=1 THEN 3 ELSE 4 END;

If the value of the instance specified in an instance method invocation is null, the result of the invocation is null (SQL NULL). However, you still might need to use the CASE expression for when a nullable column is a primitive method parameter.