At 06:51 a.m. 21/03/2014, dressel@... wrote:

>FB 1.5
>The following SQL (and variations of it) consistently fail to run (where ClientNameis a domain defined as varchar(20), and Client_ID is a integer):
>
>Update ClientInfo CI
>set ClientName= 'A' + cast(CI.Client_ID as varchar(10))
>
>How can I update ClientName?

In SQL the "+" symbol is an arithmetic operator. The string concatenation operator is two pipe symbols ( || ). So:

Update ClientInfo
set ClientName= 'A' || cast(Client_ID as varchar(10))

Usually you can rely on the engine to do the cast for you. IOW, this should work too:

Update ClientInfo
set ClientName= 'A' || Client_ID

Incidentally, if you have been getting other parsing errors with your example, look at the way you are using table identifier aliases, e.g.,

Update ClientInfo CI
set CI.ClientName= 'A' || cast(CI.Client_ID as varchar(10))

i.e., if you refer to one column using the table alias then you should refer to all the other columns the same way. But the aliases are redundant in a single-table DML statement so no need to make the engine work harder than it needs to. ;-)


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__________________________________________________________________