[
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
