[
https://issues.apache.org/jira/browse/DERBY-6581?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kim Haase reassigned DERBY-6581:
--------------------------------
Assignee: Kim Haase
> 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
>
> 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 {{caseOperand}}. 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)
