[DERBY-6581] Document simple case syntax

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.11.1.1
• Fix Version/s: 10.11.1.1
• Component/s: Documentation
• Assignee: Camilla Haase
• Reporter: Knut Anders Hatlen
• Created: 22/May/14 10:59
• Updated: 03/Sep/14 08:37
• Resolved: 27/May/14 13:30

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":

CASE valueExpression
  WHEN whenOperand [, whenOperand ]* THEN thenExpression
  [ WHEN whenOperand [, whenOperand ]* THEN thenExpression ]*
  [ ELSE elseExpression ]
END

whenOperand could be defined like this:

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

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:

-- 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

Issue Links

• is part of: DERBY-1576 Extend the CASE expression syntax for "simple case"