📄 conditionalexpressions.htm
字号:
<html>
<head>
<title>Conditional Expressions</title>
</head>
<!--#include virtual="/inc/header.php"-->
<table width="100%" border="0" cellspacing="0" cellpadding="2" bgcolor="#FFFFFF">
<tr>
<td align="left">
<span style="font-family:Helvetica,Arial; font-size:12pt; color:#000000"><b>Conditional Expressions
<br>
</b></span>
</td>
<td align="right">
<font face="Arial" size="2">
<a href="systemfunctions.htm">Previous</a>
<a href="overview.htm">Top</a>
<a href="selectstatement.htm">Next</a>
</font>
</td>
</tr>
</table>
<br><br>
<span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
Absolute Database provides the following functions to convert data from one data type to another:
<br>
<br>
<table border="0" cellpadding="1"><tr><td align="left" valign="top" width="84">
<span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><b><u>Function</u></b>
<br>
</span></td><td align="left" valign="top" width="262">
<span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><b><u>Description</u></b>
<br>
</span></td></tr><tr><td align="left" valign="top" width="84">
<span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">CASE
<br>
</span></td><td align="left" valign="top" width="262">
<span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">Evaluates a list of conditions and returns one of multiple possible result expressions
<br>
</span></td></tr><tr><td align="left" valign="top" width="84">
<span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">COALESCE
<br>
</span></td><td align="left" valign="top" width="262">
<span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">Returns the first non-NULL expression from a list
<br>
</span></td></tr></table>
<br>
<br>
<br>
<b>CASE</b>
<br>
<br>
Evaluates a list of conditions and returns one of multiple possible result expressions.
<br>
CASE has two formats:
<br>
<table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><font face="Symbol" size="2" color="#000000">·</font></span></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">The simple CASE function compares an expression to a set of simple expressions to determine the result.
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><font face="Symbol" size="2" color="#000000">·</font></span></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">The searched CASE function evaluates a set of Boolean expressions to determine the result.
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"></span></td><td></td></tr></table><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">Both formats support an optional ELSE argument.
<br>
<br>
<b>Simple CASE function:</b>
<br>
<br>
CASE <i>input_expression</i>
<br>
WHEN <i>when_expression</i> THEN <i>result_expression</i>
<br>
[ ...n ]
<br>
[
<br>
ELSE <i>else_result_expression</i>
<br>
]
<br>
END
<br>
<br>
<b>Searched CASE function:</b>
<br>
<br>
CASE
<br>
WHEN <i>Boolean_expression</i> THEN <i>result_expression</i>
<br>
[ ...n ]
<br>
[
<br>
ELSE <i>else_result_expression</i>
<br>
]
<br>
END
<br>
<br>
<i>input_expression</i>
<br>
Is the expression evaluated when using the simple CASE format. <i>input_expression</i> is any valid expression.
<br>
<br>
WHEN <i>when_expression</i>
<br>
Is a simple expression to which <i>input_expression</i> is compared when using the simple CASE format. <i>when_expression</i> is any valid expression. The data types of <i>input_expression</i> and each <i>when_expression</i> must be the same or must be an implicit conversion.
<br>
<br>
n
<br>
Is a placeholder indicating that multiple WHEN when_expression THEN result_expression clauses, or multiple WHEN <i>Boolean_expression</i> THEN <i>result_expression</i> clauses can be used.
<br>
<br>
THEN <i>result_expression</i>
<br>
Is the expression returned when <i>input_expression</i> equals <i>when_expression</i> evaluates to TRUE, or <i>Boolean_expression</i> evaluates to TRUE. <i>result expression</i> is any valid expression.
<br>
<br>
ELSE <i>else_result_expression</i>
<br>
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. <i>else_result_expression</i> is any valid expression. The data types of <i>else_result_expression </i>and <i>any result_expression</i> must be the same or must be an implicit conversion.
<br>
<br>
WHEN <i>Boolean_expression</i>
<br>
Is the Boolean expression evaluated when using the searched CASE format. <i>Boolean_expression</i> is any valid Boolean expression.
<br>
<br>
<br>
<b>Result Values</b>
<br>
<br>
<b>Simple CASE function:
<br>
</b>
<br>
<table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><font face="Symbol" size="2" color="#000000">·</font></span></span></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">Evaluates <i>input_expression</i>, and then, in the order specified, evaluates <i>input_expression</i> = <i>when_expression</i> for each WHEN clause.
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><font face="Symbol" size="2" color="#000000">·</font></span></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">Returns the <i>result_expression</i> of the first (<i>input_expression</i> = <i>when_expression</i>) that evaluates to TRUE.
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><font face="Symbol" size="2" color="#000000">·</font></span></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">If no <i>input_expression</i> = <i>when_expression</i> evaluates to TRUE, Absolute Database returns the <i>else_result_expression</i> if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"></span></td><td></td></tr></table><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
<b>Searched CASE function:
<br>
</b>
<br>
<table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><font face="Symbol" size="2" color="#000000">·</font></span></span></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">Evaluates, in the order specified, <i>Boolean_expression</i> for each WHEN clause.
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><font face="Symbol" size="2" color="#000000">·</font></span></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">Returns <i>result_expression</i> of the first <i>Boolean_expression</i> that evaluates to TRUE.
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><font face="Symbol" size="2" color="#000000">·</font></span></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">If no <i>Boolean_expression</i> evaluates to TRUE, Absolute Database returns the <i>else_result_expression</i> if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"></span></td><td></td></tr></table><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
<b>Examples:</b>
<br>
<br>
SELECT first_name, last_name,
<br>
CASE state
<br>
WHEN 'CA' THEN 'California'
<br>
WHEN 'KS' THEN 'Kansas'
<br>
WHEN 'TN' THEN 'Tennessee'
<br>
WHEN 'OR' THEN 'Oregon'
<br>
WHEN 'MI' THEN 'Michigan'
<br>
WHEN 'IN' THEN 'Indiana'
<br>
WHEN 'MD' THEN 'Maryland'
<br>
WHEN 'UT' THEN 'Utah'
<br>
END AS StateName
<br>
FROM clients
<br>
ORDER BY last_name
<br>
<br>
<br>
SELECT Price,
<br>
CASE
<br>
WHEN price IS NULL THEN 'Not yet priced'
<br>
WHEN price < 10 THEN 'Very Reasonable Title'
<br>
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
<br>
ELSE 'Expensive book!'
<br>
END AS 'Price Category'
<br>
FROM titles
<br>
ORDER BY price
<br>
<br>
<br>
<b>COALESCE (value_expr [ , value_expr ... ] ) </b>
<br>
</span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">Returns the first non-NULL expression from a list.
<br>
</span><span style="font-family:Helvetica,Arial; font-size:4pt; color:#000000">
<br>
</span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"><b>Examples:</b>
<br>
<table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000"></span></span><span style="font-family:Courier New; font-size:8pt; color:#000000">COALESCE(NULL, 34, 13) returns 34
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:8pt; color:#000000">COALESCE(FirstName, 'N/A') returns FirstName value if it's not null, or 'N/A' otherwise
<br>
</span></td></tr></table><table width="100%" border="0" cellpadding="0" cellspacing="0"><tr valign="top"><td width="14"></td><td><span style="font-family:Courier New; font-size:8pt; color:#000000"></span></td></tr></table><span style="font-family:Courier New; font-size:8pt; color:#000000"></span><span style="font-family:Helvetica,Arial; font-size:10pt; color:#000000">
<br>
<br>
</span></span>
<!--#include virtual="/inc/footer.php"-->
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -