[
https://issues.apache.org/jira/browse/DERBY-6581?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-6581:
--------------------------------------
Description:
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}
was:
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}
> 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 {{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)
