# Conditional expressions v16

SQL-compliant conditional expressions are available in EDB Postgres Advanced Server.

## CASE

The SQL `CASE`

expression is a generic conditional expression, similar to if/else statements in other languages:

You can use `CASE`

clauses wherever an expression is valid. `condition`

is an expression that returns a `BOOLEAN`

result. If the result is `TRUE`

, then the value of the `CASE`

expression is the `result`

that follows the condition. If the result is `FALSE`

, any subsequent `WHEN`

clauses are searched in the same manner. If `no WHEN condition`

is `TRUE`

, then the value of the `CASE`

expression is the `result`

in the `ELSE`

clause. If the `ELSE`

clause is omitted and no condition matches, the result is `NULL`

.

For example:

The data types of all the `result`

expressions must be convertible to a single output type.

The following simple `CASE`

expression is a specialized variant of the general form:

The `expression`

is computed and compared to all the `value`

specifications in the `WHEN`

clauses until one is found that is equal. If no match is found, the `result`

in the `ELSE`

clause (or a null value) is returned.

This same example can be written using the simple `CASE`

syntax:

A `CASE`

expression doesn't evaluate any subexpressions that aren't needed to determine the result. For example, this is a possible way of avoiding a division-by-zero failure:

## COALESCE

The `COALESCE`

function returns the first of its arguments that isn't null. Null is returned only if all arguments are null.

It's often used to substitute a default value for null values when data is retrieved for display or further computation. For example:

Like a `CASE`

expression, `COALESCE`

doesn't evaluate arguments that aren't needed to determine the result. Arguments to the right of the first non-null argument aren't evaluated. This SQL-standard function provides capabilities similar to `NVL`

and `IFNULL`

, which are used in some other database systems.

## NULLIF

The `NULLIF`

function returns a null value if `value1`

and `value2`

are equal. Otherwise it returns `value1`

.

You can use this to perform the inverse operation of the `COALESCE`

example:

If `value1`

is (none), return a null. Otherwise return `value1`

.

## NVL

The `NVL`

function returns the first of its arguments that isn't null. `NVL`

evaluates the first expression. If that expression evaluates to `NULL`

, `NVL`

returns the second expression.

The return type is the same as the argument types. All arguments must have the same data type or be coercible to a common type. `NVL`

returns `NULL`

if all arguments are `NULL`

. `''`

is considered as unknown, and if the arguments data type aren't coercible to the common data type, then `NVL`

throws an error.

### Examples

This example computes a bonus for noncommissioned employees. If an employee is a commissioned employee, this expression returns the employee's commission. If the employee isn't a commissioned employee, that is, their commission is `NULL`

, this expression returns a bonus that's 10% of their salary.

In this example, the type of `1`

is numeric and the type of `''`

is considered as unknown. Therefore PostgreSQL decides that the common type is numeric. It tries to interpret the empty string as a numeric value, which produces the indicated error:

In this example, if `33`

is type casted to double precision, it converts to double precision and returns the value as double precision. If `33`

is type casted to numeric, it converts to numeric and returns the value as numeric.

## NVL2

`NVL2`

evaluates an expression and returns either the second or third expression, depending on the value of the first expression. If the first expression isn't `NULL`

, `NVL2`

returns the value in `expr2`

. If the first expression is `NULL`

, `NVL2`

returns the value in `expr3`

.

The return type is the same as the argument types. All arguments must have the same data type or be coercible to a common type.

This example computes a bonus for commissioned employees. If a given employee is a commissioned employee, this expression returns an amount equal to 110% of their commission. If the employee isn't a commissioned employee, that is, their commission is `NULL`

, this expression returns `0`

.

## NANVL

The `NANVL`

function returns the first of its arguments that is not-a-number (NaN) value. `NANVL`

evaluates the first expression. If that expression evaluates to a NAN value, `NANVL`

returns the second expression. If the first expression evaluates to a number value, `NANVL`

returns the first expression. The `NANVL`

function is useful only for floating-point numbers of type `BINARY_FLOAT`

or `BINARY_DOUBLE`

.

This function takes any numeric data type or any non-numeric data type that can be implicitly converted to a numeric data type as arguments. EDB Postres Advanced Server determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

### Examples

## LNNVL

The `LNNVL`

function provides a concise way to evaluate a condition when one or both operands of the condition might be null.

The function returns `TRUE`

if the condition is `FALSE`

or `UNKNOWN`

and `FALSE`

if the condition is `TRUE`

.

The condition can evaluate any scalar values but can't be a compound condition containing `AND`

, `OR`

, or `BETWEEN`

.

Use the `LNNVL`

function:

- In the
`WHERE`

clause of a query. - In the
`WHEN`

condition in a searched`CASE`

expression. - Anywhere a scalar expression appears, even in contexts where the
`IS [NOT] NULL`

,`AND`

, or`OR`

conditions aren't valid but otherwise are required to account for potential nulls.

### Examples

Create a table and insert data:

Use the `LNNVL`

function in a `SELECT`

query:

## GREATEST and LEAST

The `GREATEST`

and `LEAST`

functions select the largest or smallest value from a list of any number of expressions.

All of the expressions must be convertible to a common data type, which becomes the type of the result. Null values in the list are ignored. The result is null only if all the expressions evaluate to null.

##### Note

The `GREATEST`

and `LEAST`

aren't in the SQL standard but are a common extension.