DSQL, PSQL

    Syntax

    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.

    1. select
    2.   coalesce (Nickname, FirstName, 'Mr./Mrs.') || ' ' || LastName
    3.     as FullName

    See also

    , NULLIF(),

    8.9.2. DECODE()

    Available in

    DSQL, PSQL

    Syntax

    1. DECODE(<testexpr>,
    2.   <expr1>, <result1>
    4.   [, <defaultresult>])

    The equivalent CASE construct:

    1. CASE <testexpr>
    2.   WHEN <expr1> THEN <result1>
    3.   [WHEN <expr2> THEN <result2> …]
    4.   [ELSE <defaultresult>]
    5. END
    Table 157. DECODE Function Parameters
    ParameterDescription

    testexpr

    An expression of any compatible type that is compared to the expressions expr1, expr2 …​ exprN

    expr1, expr2, …​ exprN

    Expressions of any compatible types, to which the testexpr expression is compared

    result1, result2, …​ resultN

    Returned values of any type

    defaultresult

    The expression to be returned if none of the conditions is met

    Result type

    Varies

    Description

    DECODE is a shorthand for the so-called , 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

    See also

    CASE,

    Available in

    DSQL, PSQL

    Syntax

      Table 158. IIF Function Parameters
      ParameterDescription

      condition

      A true|false expression

      resultT

      The value returned if the condition is true

      resultF

      The value returned if the condition is false

      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

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

      See also

      CASE,

      8.9.4. MAXVALUE()

      Available in

      DSQL, PSQL

      Syntax

      Table 159. MAXVALUE Function Parameters
      ParameterDescription

      expr1 …​ exprN

      List of expressions of compatible types

      Result type

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

      Description

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

      Example

      See also

      Available in

      DSQL, PSQL

      Syntax

      1. 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

      1. SELECT MINVALUE(PRICE_1, PRICE_2) AS PRICE
      2.   FROM PRICELIST

      See also

      MAXVALUE()

      8.9.6. NULLIF()

      Available in

      DSQL, PSQL

      Syntax

      1. NULLIF (<exp1>, <exp2>)
      Table 161. NULLIF Function Parameters
      ParameterDescription

      exp1

      An expression

      exp2

      Another expression of a data type compatible with exp1

      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

      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.

      COALESCE(), , IIF(),