db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag.Wan...@Sun.COM (Dag H. Wanvik)
Subject Re: VALUE a synonym for COALESCE, document?
Date Mon, 10 Aug 2009 23:28:27 GMT

Hi Kim!

Kim Haase <Camilla.Haase@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.


View raw message