Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.11.1.1
    • Fix Version/s: 10.11.1.1
    • Component/s: Documentation
    • Labels:
      None

      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
      
      1. rrefcase.html
        9 kB
        Kim Haase
      2. DERBY-6581-2.diff
        5 kB
        Kim Haase
      3. DERBY-6581.diff
        5 kB
        Kim Haase
      4. rrefcase.html
        9 kB
        Kim Haase

        Issue Links

          Activity

          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Kim Haase made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Issue & fix info Patch Available [ 10102 ]
          Fix Version/s 10.11.0.0 [ 12324243 ]
          Resolution Fixed [ 1 ]
          Kim Haase made changes -
          Attachment DERBY-6581-2.diff [ 12646601 ]
          Attachment rrefcase.html [ 12646602 ]
          Kim Haase made changes -
          Issue & fix info Patch Available [ 10102 ]
          Kim Haase made changes -
          Attachment rrefcase.html [ 12646553 ]
          Attachment DERBY-6581.diff [ 12646554 ]
          Knut Anders Hatlen made changes -
          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 {{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}
          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}
          Kim Haase made changes -
          Assignee Kim Haase [ chaase3 ]
          Knut Anders Hatlen made changes -
          Field Original Value New Value
          Link This issue is part of DERBY-1576 [ DERBY-1576 ]
          Knut Anders Hatlen created issue -

            People

            • Assignee:
              Kim Haase
              Reporter:
              Knut Anders Hatlen
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development