db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF subversion and git services (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6581) Document simple case syntax
Date Tue, 27 May 2014 13:30:03 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14009659#comment-14009659
] 

ASF subversion and git services commented on DERBY-6581:
--------------------------------------------------------

Commit 1597780 from [~chaase3] in branch 'docs/trunk'
[ https://svn.apache.org/r1597780 ]

DERBY-6581  Document simple case syntax

Modified a Reference Manual topic.

Patches: DERBY-6581-2.diff

> Document simple case syntax
> ---------------------------
>
>                 Key: DERBY-6581
>                 URL: https://issues.apache.org/jira/browse/DERBY-6581
>             Project: Derby
>          Issue Type: Improvement
>          Components: Documentation
>    Affects Versions: 10.11.0.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Kim Haase
>         Attachments: DERBY-6581-2.diff, DERBY-6581.diff, rrefcase.html, rrefcase.html
>
>
> We should document the new syntax added in DERBY-1576 in the CASE expression topic of
the reference manual.
> The variant that is currently documented in the CASE expression topic, is called "searched
case". It should still be documented. In addition, the topic should show this syntax for "simple
case":
> {noformat}
> CASE valueExpression
>   WHEN whenOperand [, whenOperand ]* THEN thenExpression
>   [ WHEN whenOperand [, whenOperand ]* THEN thenExpression ]*
>   [ ELSE elseExpression ]
> END
> {noformat}
> {{whenOperand}} could be defined like this:
> {noformat}
> valueExpression |
> { < | = | > | <= | >= | <> } expression |
> IS [ NOT ] NULL |
> [ NOT ] LIKE characterExpression WithWildCard [ ESCAPE 'escapeCharacter'] |
> [ NOT ] BETWEEN expression AND expression |
> [ NOT ] IN tableSubquery |
> [ NOT ] IN ( expression [, expression ]* ) |
> comparisonOperator { ALL | ANY | SOME } tableSubquery
> {noformat}
> Most of this syntax is copied from the table in the Boolean expressions topic, so we
may try to find some way to refactor it to avoid repetition here.
> There are some inconsistencies in this definition of {{whenOperand}}. One is that I think
all occurrences of "expression" could be replaced with "valueExpression". Another is that
line 2 lists all comparison operators explicitly, whereas line 8 uses the shorthand "comparisonOperator".
I suppose both should do the same. If we choose to use the shorthand, we should probably also
define comparisonOperator somewhere.
> Examples:
> {code:sql}
> -- returns 'two'
> VALUES
>   CASE 1+1
>     WHEN 1 THEN 'one'
>     WHEN 2 THEN 'two'
>     ELSE 'many'
>   END
> -- 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)
> -- 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
> {code}



--
This message was sent by Atlassian JIRA
(v6.2#6252)

Mime
View raw message