Re: cpyfrmimpf

On 08 Jan 2013 16:50, A Paul wrote:

<<SNIP>>

CASE1:
=======
create table DEVLIB/localpf
( date1 date, client_id varchar(30), principal varchar(12)
, date2 date, balance varchar(15) , type char(10)
, date3 date, net_amount varchar(15) )

When I define net_amount field as VARCHAR (15), I get below values
into (net_amount) from FTPCSVF after I do

<<SNIP>>

net_amount (LOCALPF)

11,053.42
22,696.53
46,020.83
37,978.75
4,219.86
-419,665.67
36,746.84
-1,641.62

and on subsequent SQL to update
(Updating AFile NetAmt(defined as 15,2)
from LOCALPF(Bfile) net_amount s)

create table qtemp/AFile (NetAmt numeric(15, 2), date1 date)
; -- this describes all we need to know about AFile.
create alias qtemp/BFile for DEVLIB/localpf
; -- this clarifies the reference to LOCALPF as BFile below

UPDATE Afile A
SET A.Netamt = (SELECT B.net_amount
FROM Bfile B
WHERE A.date1 = B.date1)
WHERE EXISTS
(SELECT B.net_amount
FROM Bfile B
WHERE A.date1 = B.date1)

<<SNIP>>

I get an error "Character in CAST argument not valid"...

Yes. That is what I had warned of in the following reply:
http://archive.midrange.com/rpg400-l/201301/msg00053.html
"...
a string representation of a numeric value which includes a thousand separator "is invalid for numeric representation"; i.e. the non-delimited string values such as "-15,879.47" and "154,700.00" can not be CAST [explicitly nor implicitly] into a numeric data type by the SQL. Assuming the values have a consistent representation, 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, ',', '')
FROM Bfile B
WHERE A.date1 = B.date1)
WHERE EXISTS
(SELECT B.net_amount
FROM Bfile B
WHERE A.date1 = B.date1)

This modified UPDATE statement uses the REPLACE scalar function to replace the comma as thousand separator, replaced with the empty string. The given expression ensures each of the values listed as example string representations of numeric data for the column net_amount (LOCALPF) will become a valid representation of a number... and thus all of the characters in the CAST function should be valid.

CASE 2:
<<SNIP>>

can please advise how do I resolve the error in Case 1 (OR)

Inline above.

How do I get the correct values as that of incoming file in Case 2?

That described scenario, the best I can determine, exhibits a defect with the results of the CPYFRMIMPF. The invalid data should cause the data to fail with an error for CAST from the character string into a FLOAT data type; i.e. CPF2845 RC7. No matter... As I had noted in a prior message, the invalid numeric data that had been exported, can only be imported into a character field; i.e. character data with a thousand separator can not be imported into a numeric column.