Re: cpyfrmimpf

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>>