SQL Reference

Chapter 2 | Supported standards 34
Conditional functions
Conditional
functions Description Example
CASE WHEN Simple CASE format
Compares the value of input_exp to the values
of value_exp arguments to determine the result.
CASE input_exp
{WHEN value_exp THEN result...} [ELSE
result]
END
SELECT
Invoice_ID,
CASE Company_Name
WHEN 'Exports UK' THEN
'Exports UK Found'
WHEN 'Home Furniture
Suppliers' THEN 'Home Furniture
Suppliers Found'
ELSE 'Neither Exports UK
nor Home Furniture Suppliers'
END,
Salesperson_ID
FROM
Sales_Data
Searched CASE format
Returns a result based on whether the condition
specified by a WHEN expression is true.
CASE
{WHEN boolean_exp THEN result...} [ELSE
result]
END
SELECT
Invoice_ID,
Amount,
CASE
WHEN Amount > 3000 THEN 'Above
3000'
WHEN Amount < 1000 THEN 'Below
3000'
ELSE 'Between 1000 and 3000'
END,
Salesperson_ID
FROM
Sales_Data
COALESCE Returns the first value that is not NULL SELECT
Salesperson_ID,
COALESCE(Sales_Manager,
Salesperson)
FROM
Salespeople
NULLIF Compares two values and returns NULL if the two
values are equal; otherwise, returns the first
value.
SELECT
Invoice_ID,
NULLIF(Amount, -1),
Salesperson_ID
FROM
Sales_Data