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-1306) Support more than just varchar with vertical bars on query
Date Wed, 18 Oct 2006 21:27:37 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1306?page=comments#action_12443365 ] 
Christian d'Heureuse commented on DERBY-1306:

The Derby documentation of the concatenation operator at http://db.apache.org/derby/docs/dev/ref/rrefsqlj40899.html
"Because all built-in data types are implicitly converted to strings, this function can act
on all built-in data types."

This is currently not true. Values of type integer, which is a built-in data type, are not
implicitly converted into strings with the concat operator.

The SQL statement

&nbsp;    VALUES 'abc' || 123 

produces the error:

&nbsp;   Cannot convert types 'INTEGER' to 'VARCHAR'.

The following statement works, but it's inconvenient to use RTRIM(CHAR()) for all non-string

&nbsp;  VALUES 'abc' || RTRIM(CHAR(123)) || 'xyz'

> Support more than just varchar with vertical bars on query
> ----------------------------------------------------------
>                 Key: DERBY-1306
>                 URL: http://issues.apache.org/jira/browse/DERBY-1306
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions:
>         Environment: Relevant for this?  Windows XP
>            Reporter: Bill Robertson
> I'm not sure if this is ij or derby proper, but the concat operator || should work for
just about any data type.  Derby is zero maintenance, but data rarely is, and a good sql interface
is important.  Often it's very convenient to write a query that generates other sql statements
and that's difficult w/o concatination.
> e.g. create a table with a timestamp column, assume that the column name is originated.
> Try the following query:
> ij> select month(originated) || '-' || year(originated) from review;
> get the following error:
> ERROR 42846: Cannot convert types 'INTEGER' to 'VARCHAR'.
> The conversion to a string should pretty much work all the time unless the operand is
some sort of blob type.

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


View raw message