8.9. Conditional Functions
8.9.1. COALESCE()
Returns the first non-NULL
argument
Result typeDepends on input
Syntax
|
COALESCE (<exp1>, <exp2> [, <expN> ... ])
COALESCE
Function ParametersParameter | Description |
---|---|
exp1, exp2 … expN | A list of expressions of compatible types |
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
.
8.9.1.1. COALESCE
Examples
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
,
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.
This scheme only works if absent nicknames and first names are 'Mr./Mrs.'
NULL
: if one of them is an empty string, COALESCE
will happily return that to the caller.
That problem can be fixed by using Section 8.9.6, “NULLIF()
”.
|select
|coalesce (Nickname, FirstName, 'Mr./Mrs.') || ' ' || LastName
|as FullName
|from Persons
See alsoSection 8.9.3, “IIF()
”, Section 8.9.6, “NULLIF()
”, CASE
8.9.2. DECODE()
Shorthand simple
-equivalent functionCASE
Result typeDepends on input
Syntax
|
DECODE(<testexpr>,
| <expr1>, <result1>
| [<expr2>, <result2> ...]
| [, <defaultresult>])
DECODE
Function ParametersParameter | Description |
---|---|
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 |
DECODE
is a shorthand for the so-called simple
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 CASE
NULL
is returned.
The equivalent CASE
construct:
|CASE <testexpr>
|WHEN <expr1> THEN <result1>
|[WHEN <expr2> THEN <result2> ...]
|[ELSE <defaultresult>]
|END
Matching is done with the
operator, so if testexpr is =
NULL
, it won’t match any of the exprs, not even those that are NULL
.
8.9.2.1. DECODE
Examples
|select name,
|age,
|decode(upper(sex),
|'M', 'Male',
|'F', 'Female',
|'Unknown'),
|religion
|from people
See alsoCASE
, Simple CASE
8.9.3. IIF()
Ternary conditional function
Result typeDepends on input
Syntax
|
IIF (<condition>, ResultT, ResultF)
IIF
Function ParametersParameter | Description |
---|---|
condition | A true|false expression |
resultT | The value returned if the condition is true |
resultF | The value returned if the condition is false |
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.<condition> ? resultT : resultF
IIF(<condition>, resultT, resultF)
is a shorthand for
.CASE WHEN <condition> THEN resultT ELSE resultF END
8.9.3.1. IIF
Examples
|select iif( sex = 'M', 'Sir', 'Madam' ) from Customers
See alsoCASE
, Section 8.9.2, “DECODE()
”
8.9.4. MAXVALUE()
Returns the maximum value of its arguments
Result typeVaries according to input — result will be of the same data type as the first expression in the list (expr1).
Syntax
|
MAXVALUE (<expr1> [, ... , <exprN> ])
MAXVALUE
Function ParametersParameter | Description |
---|---|
expr1 … exprN | List of expressions of compatible types |
Returns the maximum value from a list of numerical, string, or date/time expressions.
This function fully supports text BLOB
s 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
.
8.9.4.1. MAXVALUE
Examples
|SELECT MAXVALUE(PRICE_1, PRICE_2) AS PRICE
|FROM PRICELIST
See alsoSection 8.9.5, “MINVALUE()
”
8.9.5. MINVALUE()
Returns the minimum value of its arguments
Result typeVaries according to input — result will be of the same data type as the first expression in the list (expr1).
Syntax
|
MINVALUE (<expr1> [, ... , <exprN> ])
MINVALUE
Function ParametersParameter | Description |
---|---|
expr1 … exprN | List of expressions of compatible types |
Returns the minimum value from a list of numerical, string, or date/time expressions.
This function fully supports text BLOB
s 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
.
8.9.5.1. MINVALUE
Examples
|SELECT MINVALUE(PRICE_1, PRICE_2) AS PRICE
|FROM PRICELIST
See alsoSection 8.9.4, “MAXVALUE()
”
8.9.6. NULLIF()
Conditional NULL
function
Result typeDepends on input
Syntax
|
NULLIF (<exp1>, <exp2>)
NULLIF
Function ParametersParameter | Description |
---|---|
exp1 | An expression |
exp2 | Another expression of a data type compatible with exp1 |
NULLIF
returns the value of the first argument, unless it is equal to the second.
In that case, NULL
is returned.
8.9.6.1. NULLIF
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 alsoSection 8.9.1, “COALESCE()
”, Section 8.9.2, “DECODE()
”, Section 8.9.3, “IIF()
”, CASE