db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From chaa...@apache.org
Subject svn commit: r1597780 - /db/derby/docs/trunk/src/ref/rrefcase.dita
Date Tue, 27 May 2014 13:28:42 GMT
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>{ &lt; | = | > | &lt;= | >= | &lt;> }</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 &lt; 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>



Mime
View raw message