|
CASE Advantage SQL Engine |
|
|
|
CASE Advantage SQL Engine |
|
|
The CASE expression evaluates a list of conditions and returns one of multiple possible result expressions. The CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements.
CASE has two formats:
| • | The simple CASE function compares an expression to a set of simple expressions to determine the result. |
| • | The searched CASE function evaluates a set of Boolean expressions to determine the result. |
Both formats support an optional ELSE argument.
Syntax
The BNF syntax of CASE expression can be found in Advantage SQL Grammar.
Simple CASE function:
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
Searched CASE function:
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
Arguments
input_expression
Is the expression evaluated when using the simple CASE format. input_expression is any valid Advantage SQL expression.
WHEN when_expression
Is a simple expression to which input_expression is compared when using the simple CASE format. when_expression is any valid Advantage SQL expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.
n
Is a placeholder indicating that multiple WHEN when_expression THEN result_expression clauses, or multiple WHEN Boolean_expression THEN result_expression clauses can be used.
THEN result_expression
Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid Advantage SQL expression.
ELSE else_result_expression
Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid Advantage SQL expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.
WHEN Boolean_expression
Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.
Result Types
Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.
Result Values
Simple CASE function:
| • | Evaluates input_expression, and then, in the order specified, evaluates input_expression = when_expression for each WHEN clause. |
| • | Returns the result_expression of the first (input_expression = when_expression) that evaluates to TRUE. |
| • | If no input_expression = when_expression evaluates to TRUE, Advantage returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified. |
Searched CASE function:
| • | Evaluates, in the order specified, Boolean_expression for each WHEN clause. |
| • | Returns result_expression of the first Boolean_expression that evaluates to TRUE. |
| • | If no Boolean_expression evaluates to TRUE, Advantage returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified. |
Examples
A. Use a SELECT statement with a simple CASE function
This example uses the CASE function to alter the display of the marriage status of the employees to make them more understandable.
SELECT Lastname + firstname As "Employee Name",
CASE married
WHEN True THEN 'Married'
WHEN False THEN 'Single'
ELSE 'Not yet known'
END As "Marital status"
FROM Employee
WHERE DateOfBirth >= '1970-03-31'
ORDER BY 2
Here is the result set:
Employee Name Marital Status
------------------- -------------------------
Smith John Married
Coles Becky Not yet known
Holmes Monique Single
Brown Terry Single
B. Use a SELECT statement with a searched CASE function
This example displays the current vacation earning schedule of the employees based on how long the employee has been working for the company.
SELECT Lastname + firstname As "Employee Name",
CASE
WHEN TimestampDiff( SQL_TSI_YEAR, CURR_DATE(), DateOfHire ) >= 10 THEN 7
WHEN TimestampDiff( SQL_TSI_YEAR, CURR_DATE(), DateOfHire ) >= 5 THEN 6.66
ELSE 5
END As "Vacation Hours Earned This period"
FROM Employee
ORDER BY 2 DESC
Here is the result set:
Employee Name Vacation Hours Earned this Period
--------------------- ---------------------------------
Schmidt Kathy 7.00
Wilkins Becky 6.66
Wong Wesley 5
Schmidt Mark 5
See Also