[
https://issues.apache.org/jira/browse/DERBY-6581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14006342#comment-14006342
]
Kim Haase commented on DERBY-6581:
----------------------------------
Thanks for the very thorough description, Knut. A few questions ...
1) Currently instead of "whenOperand" we use "booleanExpression", linking to
the "Boolean expressions" topic. Is there some reason to provide a complete
syntax of boolean expressions in the CASE expression topic? It might be simpler
just to point out any Boolean expressions that are not permitted in a CASE
expression (if any).
2) Is the syntax of "whenOperand" entirely correct? It appears that one of the
possibilities is a construction consisting of just a comparison operator
followed by an expression, when there has to be an expression on either side of
the operator, doesn't there? And are AND, OR, NOT expressions not permitted?
3) In one paragraph you mention the definition of "caseOperand" -- did you mean
"whenOperand"?
> 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)
