You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Dag H. Wanvik" <Da...@Sun.COM> on 2009/08/07 00:17:47 UTC
VALUE a synonym for COALESCE, document?
I see that VALUE is a synonym for COALESCE in Derby (same as DB2). I
don't think this is standard SQL. I can't find it documented. Should
we document it?
Dag
Re: VALUE a synonym for COALESCE, document?
Posted by Kim Haase <Ca...@Sun.COM>.
Thank you, Dag, that answers the questions very clearly!
I will add this information to the issue and work on it.
Kim
On 08/10/09 19:28, Dag H. Wanvik wrote:
> Hi Kim!
>
> Kim Haase <Ca...@Sun.COM> writes:
>
>> Neither COALESCE nor VALUE is documented currently (except for a
>> mention in an error message).
>>
>> Looking at DERBY-495, it appears that the doc effort stalled for lack
>> of answers to a couple questions --
>>
>> I gather VALUE as a synonym of COALESCE is not in the SQL standard,
>> but COALESCE itself is? Should we mention VALUE, then?
>
> Yes, since it is implemented we should document it as a synonym, but
> probably recommend the standard form COALESCE.
>
>> What does the SQL standard say about the one-argument form? Is it
>> permitted?
>
> The standard does not allow the one-argument form:
>
> SQL 2003, vol 2 section 6.1 <case expression>:
> :
> <case abbreviation> ::=
> NULLIF <left paren> <value expression> <comma> <value expression> <right paren>
> | COALESCE <left paren> <value expression> { <comma> <value expression> }... <right paren>
>
> This means that 2 or more args are allowed.
> Derby does not permit one argument; it throws 42605. Also, not all arguments
> can be dynamic (?), if so Derby throws 42610.
>
>
> Dag
Re: VALUE a synonym for COALESCE, document?
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Hi Kim!
Kim Haase <Ca...@Sun.COM> writes:
> Neither COALESCE nor VALUE is documented currently (except for a
> mention in an error message).
>
> Looking at DERBY-495, it appears that the doc effort stalled for lack
> of answers to a couple questions --
>
> I gather VALUE as a synonym of COALESCE is not in the SQL standard,
> but COALESCE itself is? Should we mention VALUE, then?
Yes, since it is implemented we should document it as a synonym, but
probably recommend the standard form COALESCE.
>
> What does the SQL standard say about the one-argument form? Is it
> permitted?
The standard does not allow the one-argument form:
SQL 2003, vol 2 section 6.1 <case expression>:
:
<case abbreviation> ::=
NULLIF <left paren> <value expression> <comma> <value expression> <right paren>
| COALESCE <left paren> <value expression> { <comma> <value expression> }... <right paren>
This means that 2 or more args are allowed.
Derby does not permit one argument; it throws 42605. Also, not all arguments
can be dynamic (?), if so Derby throws 42610.
Dag
Re: VALUE a synonym for COALESCE, document?
Posted by Kim Haase <Ca...@Sun.COM>.
Neither COALESCE nor VALUE is documented currently (except for a mention
in an error message).
Looking at DERBY-495, it appears that the doc effort stalled for lack of
answers to a couple questions --
I gather VALUE as a synonym of COALESCE is not in the SQL standard, but
COALESCE itself is? Should we mention VALUE, then?
What does the SQL standard say about the one-argument form? Is it
permitted?
If we can resolve these, we can go ahead with documenting it.
Thanks,
Kim
On 08/10/09 11:02, Knut Anders Hatlen wrote:
> "Dag H. Wanvik" <Da...@Sun.COM> writes:
>
>> I see that VALUE is a synonym for COALESCE in Derby (same as DB2). I
>> don't think this is standard SQL. I can't find it documented. Should
>> we document it?
>
> Yes, please! :)
>
> https://issues.apache.org/jira/browse/DERBY-495
>
Re: VALUE a synonym for COALESCE, document?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
"Dag H. Wanvik" <Da...@Sun.COM> writes:
> I see that VALUE is a synonym for COALESCE in Derby (same as DB2). I
> don't think this is standard SQL. I can't find it documented. Should
> we document it?
Yes, please! :)
https://issues.apache.org/jira/browse/DERBY-495
--
Knut Anders