You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ch...@apache.org on 2014/05/27 15:28:42 UTC
svn commit: r1597780 - /db/derby/docs/trunk/src/ref/rrefcase.dita
Author: chaase3
Date: Tue May 27 13:28:42 2014
New Revision: 1597780
URL: http://svn.apache.org/r1597780
Log:
DERBY-6581 Document simple case syntax
Modified a Reference Manual topic.
Patches: DERBY-6581-2.diff
Modified:
db/derby/docs/trunk/src/ref/rrefcase.dita
Modified: db/derby/docs/trunk/src/ref/rrefcase.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefcase.dita?rev=1597780&r1=1597779&r2=1597780&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefcase.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefcase.dita Tue May 27 13:28:42 2014
@@ -19,35 +19,74 @@ limitations under the License.
-->
<reference id="rrefcase" xml:lang="en-us">
<title>CASE expression</title>
+<shortdesc>The CASE expression can be used for conditional expressions in
+<ph conref="../conrefs.dita#prod/productshortname"></ph>.</shortdesc>
<prolog><metadata>
<keywords><indexterm>CASE expression</indexterm></keywords>
</metadata></prolog>
<refbody>
-<section><p>The CASE expression can be used for conditional expressions in
-<ph conref="../conrefs.dita#prod/productshortname"></ph>.</p></section>
+<section><p>See <xref href="rrefsqlj19433.dita"/> for more information on
+expressions.</p>
+</section>
<refsyn><title>Syntax</title><p>You can place a CASE expression
anywhere an expression is allowed. It chooses an expression to evaluate based
-on a boolean test.</p> <codeblock><b>CASE
+on a boolean test.</p>
+<p><ph conref="../conrefs.dita#prod/productshortname"></ph> supports three kinds
+of CASE expressions, which we refer to as a
+<term>searched CASE expression</term>, a <term>simple CASE expression</term>,
+and an <term>extended CASE expression</term>.</p>
+<p>The syntax of a <term>searched CASE expression</term> is as follows:</p>
+<codeblock><b>CASE
WHEN <i><xref
href="rrefsqlj23075.dita#rrefsqlj23075">booleanExpression</xref></i> THEN <i>thenExpression</i>
[ WHEN <i><xref
href="rrefsqlj23075.dita#rrefsqlj23075">booleanExpression</xref></i> THEN <i>thenExpression</i> ]*
[ ELSE <i>elseExpression</i> ]
END</b></codeblock>
-<p>Both <i>thenExpression</i> and <i>elseExpression</i> are defined as
-follows:</p>
+<p>The syntax of a <term>simple CASE expression</term> is as follows:</p>
+<codeblock><b>CASE <i>valueExpression</i>
+ WHEN <i>valueExpression</i> [ , <i>valueExpression</i> ]* THEN <i>thenExpression</i>
+ [ WHEN <i>valueExpression</i> [ , <i>valueExpression</i> ]* THEN <i>thenExpression</i> ]*
+ [ ELSE <i>elseExpression</i> ]
+END</b></codeblock>
+<p>A <i>valueExpression</i> is an expression that resolves to a single
+value.</p>
+<p>For both searched and simple CASE expressions, both <i>thenExpression</i> and
+<i>elseExpression</i> are defined as follows:</p>
<codeblock><b>NULL | <i>valueExpression</i></b></codeblock>
-<p>A <i>valueExpression</i> is an expression that resolves to a single value.
-See <xref href="rrefsqlj19433.dita"/> for more information on expressions.</p>
-<p>The <i>thenExpression</i> and <i>elseExpression</i>
-must be type-compatible. For built-in types, this means
-that the types must be the same or that a built-in broadening conversion must
-exist between the types.</p>
-<p>If an ELSE clause is not specified, ELSE NULL is implicit.</p>
+<p>The <i>thenExpression</i> and <i>elseExpression</i> must be type-compatible.
+For built-in types, this means that the types must be the same or that a
+built-in broadening conversion must exist between the types.</p>
+<p>The syntax of an <term>extended CASE expression</term> is as follows:</p>
+<codeblock><b>CASE <i>valueExpression</i>
+ WHEN <i>whenOperand</i> [ , <i>whenOperand</i> ]* THEN <i>thenExpression</i>
+ [ WHEN <i>whenOperand</i> [ , <i>whenOperand</i> ]* THEN <i>thenExpression</i> ]*
+ [ ELSE <i>elseExpression</i> ]
+END</b></codeblock>
+<p>A <i>whenOperand</i> is defined as follows:</p>
+<codeblock><b><i>valueExpression</i> |
+<i>comparisonOperator</i> <i>expression</i> |
+IS [ NOT ] NULL |
+[ NOT ] LIKE <i>characterExpressionWithWildCard</i> [ ESCAPE '<i>escapeCharacter</i>' ] |
+[ NOT ] BETWEEN <i>expression</i> AND <i>expression</i> |
+[ NOT ] IN <i><xref href="rreftablesubquery.dita">tableSubquery</xref></i> |
+[ NOT ] IN ( <i>expression</i> [, <i>expression</i> ]* ) |
+<i>comparisonOperator</i> { ALL | ANY | SOME } <i><xref href="rreftablesubquery.dita">tableSubquery</xref></i></b></codeblock>
+<p>A <i>comparisonOperator</i> is defined as follows:</p>
+<codeblock><b>{ < | = | > | <= | >= | <> }</b></codeblock>
+<p>For details on LIKE expressions, see <xref href="rrefsqlj23075.dita"/>.</p>
+<p>For all types of CASE expressions, if an ELSE clause is not specified,
+ELSE NULL is implicit.</p>
</refsyn>
-<example><title>Example</title><codeblock><b>-- returns 3
+<example><title>Example</title><codeblock><b>-- searched CASE expression
+-- returns 3
VALUES CASE WHEN 1=1 THEN 3 ELSE 4 END
+-- simple CASE expression, equivalent to previous expression
+-- returns 3
+VALUES CASE 1 WHEN 1 THEN 3 ELSE 4 END
+
+-- searched CASE expression
-- returns 7
VALUES
CASE
@@ -55,6 +94,38 @@ VALUES
WHEN 4 = 5 THEN 6
ELSE 7
END
-</b></codeblock> </example>
+
+-- simple CASE expression
+-- returns 'two'
+VALUES
+ CASE 1+1
+ WHEN 1 THEN 'one'
+ WHEN 2 THEN 'two'
+ ELSE 'many'
+ END
+
+-- simple CASE expression
+-- returns 'odd', 'even', 'big'
+SELECT
+ CASE X
+ WHEN 1, 3, 5, 7, 9 THEN 'odd'
+ WHEN 2, 4, 6, 8, 10 THEN 'even'
+ ELSE 'big'
+ END
+FROM
+ (VALUES 5, 8, 12) AS V(X)
+
+-- extended CASE expression
+-- returns ('long', 182), ('medium', 340), ('short', 20)
+SELECT DISTANCE, COUNT(*)
+FROM (SELECT
+ CASE MILES
+ WHEN < 250 THEN 'short'
+ WHEN BETWEEN 250 AND 2000 THEN 'medium'
+ WHEN > 2000 THEN 'long'
+ END
+ FROM FLIGHTS) AS F(DISTANCE)
+GROUP BY DISTANCE</b></codeblock>
+</example>
</refbody>
</reference>