 ## CASE Expression

With a `CASE` expression, it is possible to specify a conditional value. Depending on the result of one or more conditional expressions, the `CASE` expression can return different values.

A `CASE` expression can be in one of the following two forms.

### CASE Expression First Form #### Rules

The following rules apply to `CASE` expressions:

• If one or more` search-conditions` are true, then the result of the `CASE` expression is the result of the first (left-most) `WHEN` clause which has a `search-condition` that is true.
• If none of the `search-conditions` are true, then the result of the `CASE` expression is the result of the explicit or implicit `ELSE` clause.
• If no `ELSE` clause is specified then `ELSE NULL` is implicit.
• At least one result in a `CASE` expression must express a value different from `NULL`.

See Result Data Types for a description of how the data type of the result of the `CASE` expression is determined.

#### Example

``` CASE WHEN col1 < 10  THEN 1
WHEN col1 >= 10 THEN 2
ELSE 3
END
```

### CASE Expression Second Form #### Rules

The following rules apply to `CASE` expressions:

• If no `ELSE` clause is specified then `ELSE NULL` is implicit.
• At least one result in a `CASE` expression must express a value different from `NULL`.

See Result Data Types for a description of how the data type of the result of the `CASE` expression is determined.

#### Example

``` CASE col1 WHEN 0 THEN NULL
WHEN -1 THEN -999
ELSE col1
END
```

### Short Forms for CASE

There are two short forms for special `CASE` expressions: `NULLIF` and `COALESCE`.

#### NULLIF where

``` NULLIF(x1, x2)

```

is equivalent to

``` CASE WHEN x1 = x2 THEN NULL ELSE x1 END

```

I.e. if the operands are equal, the `NULLIF` expression has the value `NULL`, otherwise it has the value of the first operand.

#### COALESCE where:

``` COALESCE(x1,x2)

```

is equivalent to:

``` CASE WHEN x1 IS NOT NULL THEN x1 ELSE x2
END

```

and:

``` COALESCE(x1,x2,...,xn)

```

is equivalent to:

``` CASE WHEN x1 IS NOT NULL THEN x1
ELSE COALESCE(x2,...,xn) END

```

I.e. the `COALESCE` expression returns the value of the first non-`NULL` operand, found by working from left to right, or `NULL` if all the operands equal `NULL`. Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se