8.9. Conditional Functions

8.9.1. COALESCE()

Available in

DSQL, PSQL

Syntax

COALESCE (<exp1>, <exp2> [, <expN> ... ])

Result type

Depends on input.

Description

The COALESCE function takes two or more arguments and returns the value of the first non-NULL argument. If all the arguments evaluate to NULL, the result is NULL.

Example

This example picks the Nickname from the Persons table. If it happens to be NULL, it goes on to FirstName. If that too is NULL, “'Mr./Mrs.'” is used. Finally, it adds the family name. All in all, it tries to use the available data to compose a full name that is as informal as possible. Notice that this scheme only works if absent nicknames and first names are really NULL: if one of them is an empty string instead, COALESCE will happily return that to the caller.

select
  coalesce (Nickname, FirstName, 'Mr./Mrs.') || ' ' || LastName
    as FullName
from Persons

See also

IIF(), NULLIF(), CASE

8.9.2. DECODE()

Available in

DSQL, PSQL

Syntax

DECODE(<testexpr>,
  <expr1>, <result1>
  [<expr2>, <result2> …]
  [, <defaultresult>])

The equivalent CASE construct:

CASE <testexpr>
  WHEN <expr1> THEN <result1>
  [WHEN <expr2> THEN <result2> …]
  [ELSE <defaultresult>]
END

Result type

Varies

Description

DECODE is a shorthand for the so-called “simple CASE” construct, in which a given expression is compared to a number of other expressions until a match is found. The result is determined by the value listed after the matching expression. If no match is found, the default result is returned, if present. Otherwise, NULL is returned.

Example

select name,
  age,
  decode(upper(sex),
         'M', 'Male',
         'F', 'Female',
         'Unknown'),
  religion
from people

See also

CASE, Simple CASE

8.9.3. IIF()

Available in

DSQL, PSQL

Syntax

IIF (<condition>, ResultT, ResultF)

Result type

Depends on input.

Description

IIF takes three arguments. If the first evaluates to true, the second argument is returned; otherwise the third is returned.

• IIF could be likened to the ternary “?:” operator in C-like languages.

Example

select iif( sex = 'M', 'Sir', 'Madam' ) from Customers

See also

CASE, DECODE()

8.9.4. MAXVALUE()

Available in

DSQL, PSQL

Syntax

MAXVALUE (<expr1> [, ... , <exprN> ])

Result type

Varies according to input — result will be of the same data type as the first expression in the list (expr1).

Description

Returns the maximum value from a list of numerical, string, or date/time expressions. This function fully supports text BLOBs of any length and character set.

• If one or more expressions resolve to NULL, MAXVALUE returns NULL. This behaviour differs from the aggregate function MAX.

Example

SELECT MAXVALUE(PRICE_1, PRICE_2) AS PRICE
  FROM PRICELIST

See also

MINVALUE()

8.9.5. MINVALUE()

Available in

DSQL, PSQL

Syntax

MINVALUE (<expr1> [, ... , <exprN> ])

Result type

Varies according to input — result will be of the same data type as the first expression in the list (expr1).

Description

Returns the minimum value from a list of numerical, string, or date/time expressions. This function fully supports text BLOBs of any length and character set.

• If one or more expressions resolve to NULL, MINVALUE returns NULL. This behaviour differs from the aggregate function MIN.

Example

SELECT MINVALUE(PRICE_1, PRICE_2) AS PRICE
  FROM PRICELIST

See also

MAXVALUE()

8.9.6. NULLIF()

Available in

DSQL, PSQL

Syntax

NULLIF (<exp1>, <exp2>)

Description

NULLIF returns the value of the first argument, unless it is equal to the second. In that case, NULL is returned.

Result type

Depends on input.

Example

select avg( nullif(Weight, -1) ) from FatPeople

This will return the average weight of the persons listed in FatPeople, excluding those having a weight of -1, since AVG skips NULL data. Presumably, -1 indicates “weight unknown” in this table. A plain AVG(Weight) would include the -1 weights, thus skewing the result.

See also

COALESCE(), DECODE(), IIF(), CASE