On 09 Jan 2013 07:51, A Paul wrote:
I tried the below update, it didn't work, get same error Character in
CAST argument not valid"...
UPDATE Afile A
SET A.Netamt = (SELECT replace(B.net_amount, ',', '"')
<<SNIP>>
The REPLACE scalar needs to replace the comma character [as thousand
separator, which is not valid] with a string of zero characters; i.e.
replace any commas with the null string. What was coded in the UPDATE
statement quoted above, is a request to replace the comma characters
with a double-quote character. Thus the string '11,053.42' would become
the string '11"053.42', and neither of those strings is a valid
representation of a numeric value, according to the SQL, and thus the
origin for the error.
FWiW: Consider this simplified scenario:
create table qtemp/n (n varchar(15))
;
insert into qtemp/n values
('11,053.42'),( '22,696.53'),('46,020.83'),('37,978.75')
,('4,219.86' ),('-419,665.67'),('36,746.84'),('-1,641.62')
;
update qtemp/n set n = replace( n , ',' , '')
;
select * from qtemp/n
; -- report follows:
N
11053.42
22696.53
46020.83
37978.75
4219.86
-419665.67
36746.84
-1641.62
******** End of data ********
select decimal(n, 11, 2) as N_dec from qtemp/n
; -- report follows:
N_DEC
11,053.42
22,696.53
46,020.83
37,978.75
4,219.86
419,665.67-
36,746.84
1,641.62-
******** End of data ********
CRPence on Tuesday, 8 January 2013, 23:47 wrote:
<<SNIP>> the following expression [which replaces any comma
characters with an empty string] could probably handle that concern:
replace(B.Net, ',', '')
..."
Try this SQL UPDATE request instead:
UPDATE Afile A
SET A.Netamt = (SELECT replace(B.net_amount, ',', '')
<<SNIP>>
