|
I believe I 've written more clearly so no confusion in my issue:
Thanks for the response, I used where exists on my update statement, it
seems to be working, but then I have encountered some strange scenario.
This is also follow up to my cpyfrmimpf query on midrange :
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
CPYFRMIMPF FROMFILE(DEVLIB/FTPCSVF) TOFILE(DEVLIB/LOCALPF) MBROPT
(*REPLACE) RCDDLM(*EOR) DTAFMT(*DLM) STRDLM('"') FLDDLM(',')
DECPNT(*PERIOD) DATFMT(*USA) FROMRCD(2 *END)
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)
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)
p.s: A.Netamt is defined as 15,2 on Afile (Type S)
I get an error "Character in CAST argument not valid"...
CASE 2:
========
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 FLOAT )
when I define net_amount field as FLOAT , I get the below values
after the above same CPYFRMIMPF.
net_amount(LOCALPF)
1.100000000000000E+001
2.200000000000000E+001
4.000000000000000E+000
-4.190000000000000E+002
3.600000000000000E+001
4.600000000000000E+001
3.700000000000000E+001
-1.000000000000000E+000
when I issue same update statement, I dont get any error, it
populates A.Netamt in Afile with the below values:
A.NetAmt
11.00
1.00
36.00
419.00
22.00
46.00
37.00
4.00
These values are wrong.
The incoming values from FTPCSVF are
net_amt
"11,053.42"
"-1,641.62"
"36,746.84"
"22,696.53"
"46,020.83"
"37,978.75"
"4,219.86"
can please advise how do I resolve the error in Case 1 (OR)
How do I get the correct values as that of incoming file in Case 2
?
From: A Paul <amandapaul1@xxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Sunday, 6 January 2013, 8:57
Subject: Re: cpyfrmimpf
Chuck,
Thanks I w'd try below and let you know how it goes. I'm on V6R1.
From: CRPence <CRPbottle@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Sent: Sunday, 6 January 2013, 1:34
Subject: Re: cpyfrmimpf
On 05 Jan 2013 16:52, A Paul wrote:
Below is one record of my CSV file downloaded into AS/400 as a flat12/17/2012,FIT:20111004:3739:25:50118:3,"7777.99",12/17/2012,"154,700.00",Float,12/17/2012,"-15,879.47"
file, I'm trying to load this file into externally described physical
file.
date1,client id, principal, date2, balance, type, date3, net amount
No mention of Version\Release information, but per mention of
"AS/400", presumably a very old system is being used? And while there
may be some newer release support for delimited numeric data, there
never had been in older releases... so the target field definitions
would need to be Character instead of a Numeric data type.
Additionally some of the numeric data shown, has thousand separators,
which is invalid for numeric representation. Another reason Character
data type would be required for those fields.
Finally, the Date format is *USA, not the default of *ISO, so the
DATFMT(*USA) may be required on the CPYFRMIMPF if not change to have
that as default.
CPYFRMIMPF FROMFILE(DEVlib/FTPCSVF) TOFILE(DEVLIB/LOCALPF)
MBROPT(*REPLACE) RCDDLM(*CRLF) STRDLM(*NONE) FROMRCD(2)
Only RCDDLM(*EOR) is valid for a FROMFILE that is a /QSYS.LIB file
system; i.e. a database [flat, or better, a source] file. The invalid
specification perhaps is just ignored when using FROMFILE instead of
FROMSTMF.?
With Character columns in the TOFILE being used for the delimited
numeric date in the FROMFILE, the STRDLM('"') is probably a more
appropriate specification. That string delimiter specification allows
recognition by the import feature of non-delimited *and* strings
delimited with the double-quote character.
When I did the above command CPYFRMIMPF I got just first field in
LOCALPF with error, Data from file FTPCSVF in DEVMN truncated to 1
characters.
At this point the definition of LOCALPF was undefined, so the
described effect is somewhat irrelevant.
Then I build DDS and defined schema for 8 fields in physical
file (LOCALPF) and did CPYFRMIMPF, data doesn't sit properly
due to principal, balance and net amount fields carry double
quotes. I would appreciate your help if you could provide
some solution to this.
Still the LOCALPF is left undefined. Here is an example of a SQL DDL
to define the LOCALPF that will allow the CPYFRMIMPF request that
follows that SQL, to properly import the data [row that was given]:
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) )
CPYFRMIMPF FROMFILE(DEVLIB/FTPCSVF) TOFILE(DEVLIB/LOCALPF)
MBROPT(*REPLACE) RCDDLM(*EOR) DTAFMT(*DLM) STRDLM('"') FLDDLM(',')
DECPNT(*PERIOD) DATFMT(*USA) FROMRCD(2 *END)
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
