db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Christian d'Heureuse (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1575) document full syntax of CASE expression
Date Tue, 29 Aug 2006 20:18:24 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1575?page=comments#action_12431350 ] 
            
Christian d'Heureuse commented on DERBY-1575:
---------------------------------------------

The patch contains the following lines that are not correct:

   <codeblock><b>CASE
     WHEN <i>booleanExpression</i>
     [ WHEN <i>booleanExpression</i> ]...
     THEN <i>thenExpression</i>
     ELSE <i>elseExpression</i>
   END</b></codeblock>

I suggest to change that to:

   <codeblock><b>CASE
     WHEN <i>booleanExpression</i> THEN <i>thenExpression</i>
     [ WHEN <i>booleanExpression</i>  THEN <i>thenExpression</i> ]...
     ELSE <i>elseExpression</i>
   END</b></codeblock>

In the SQL Standard ANSI/ISO/IEC 9075-2-1999, the ELSE clause is optional. So in order to
be in conformance with the SQL standard, the syntax should be:

   <codeblock><b>CASE
     WHEN <i>booleanExpression</i> THEN <i>thenExpression</i>
     [ WHEN <i>booleanExpression</i>  THEN <i>thenExpression</i> ]...
     [ ELSE <i>elseExpression</i> ]
   END</b></codeblock>

But with Derby, omitting the ELSE clause seems to work only if the value type is Character.
Examples:

 VALUES
      CASE
         WHEN 1 = 1 THEN 'a'
      END
==> OK, returns 'a'

 VALUES
      CASE
         WHEN 1 = 1 THEN 2
      END
==> Error: Types 'INTEGER' and 'CHAR' are not type compatible.

I assume that this effect is because Derby uses cast(null as char) as the default value for
elseExpression. Maybe this could be changed by using an untyped Null value as the default
value for elseExpression?

> document full syntax of CASE expression
> ---------------------------------------
>
>                 Key: DERBY-1575
>                 URL: http://issues.apache.org/jira/browse/DERBY-1575
>             Project: Derby
>          Issue Type: Improvement
>          Components: Documentation
>            Reporter: Christian d'Heureuse
>         Assigned To: Kim Haase
>         Attachments: DERBY-1575.diff
>
>
> The documentation at http://db.apache.org/derby/docs/dev/ref/rrefcasenullif.html describes
the CASE expression syntax as:
>    CASE WHEN BooleanExpression THEN thenExpression ELSE elseExpression END
> But the CASE expression supports more than one WHEN/THEN clauses.
> I suggest to change the syntax description to:
>    CASE
>       WHEN BooleanExpression THEN Expression
>     [ WHEN BooleanExpression THEN Expression ]
>       ...
>       ELSE elseExpression
>    END
> Example:
>    VALUES
>       CASE
>          WHEN 1 = 2 THEN 3
>          WHEN 4 = 5 THEN 6
>          ELSE 7
>       END

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message