Chuck , Many Thanks! I tried the below update, it didnt work, get same error Character in CAST argument not valid"...
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)
These are my steps :
1.create table DEVLIB/Bfile
( date1 date, client_id varchar(30), principal varchar(12)
, date2 date, balance varchar(15) , type char(10)
, date3 date, net_amount varchar(15) )
2. CPYFRMIMPF FROMFILE(DEVLIB/FTPCSVF) TOFILE(DEVLIB/Bfile) MBROPT
(*REPLACE) RCDDLM(*EOR) DTAFMT(*DLM) STRDLM('"') FLDDLM(',')
DECPNT(*PERIOD) DATFMT(*USA) FROMRCD(2 *END)
After the CPYFRMIMPF , it copies net_amt values from field of net_amt(external vendor told me he defined as FLOAT in schema in their system, I defined it VARCHAR(15) in create table, when I defined as FLOAT I dont get correct values ) in external FTPCSVF
"11,053.42"
"-1,641.62"
"36,746.84"
"22,696.53"
"46,020.83"
"37,978.75"
"4,219.86"
TO
Bfile
net_amount
11,053.42
22,696.53
46,020.83
37,978.75
4,219.86
-419,665.67
36,746.84
-1,641.62
I issue UPDATE statement to populate my internal master file Afile in which NetAmt defined as 15,2 S.
(Updating AFile NetAmt(defined as 15,2) from (Bfile) net_amount defined as 15 VARCHAR
===============================================================
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 does nt work I keep getting same error Character in CAST argument not valid".
Is this something that can be updated with SQL? or any alternate solution? Unfortunately our control on pulling the file is through FTP from external vendor , when it loads into my AS/400 box I see the net_amt field in double quotes, ex: below one record
FTPCSVF:
----------
date1,client id, principal, date2, balance, type, date3, net amount
12/17/2012,FIT:20111004:3739:25:50118:3,"7777.99",12/17/2012,"154,700.00",Float,12/17/2012,"-419,665.67"
Your cpyfrmimpf solution eliminates the double quotes , loads into external described PF(Bfile) nicely.
BFile:
-----
date1 client id principal date2 balance type date3 net amount
12/17/2012 FIT:20111004:3739:25:50118:3 7777.99 12/17/2012 154,700.00 Float 12/17/2012 -419,665.67
When I issue UPDATE statement to populate my internal master file(Afile), it throws an error as described above.
Thanks very much for all the help!
________________________________
From: CRPence <CRPbottle@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Sent: Tuesday, 8 January 2013, 23:47
Subject: 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.
midrange.com
