[ 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 states: "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 VALUES 'abc' || 123 produces the error: Cannot convert types 'INTEGER' to 'VARCHAR'. The following statement works, but it's inconvenient to use RTRIM(CHAR()) for all non-string values: 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: 10.1.2.1 > 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
