MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » January 2013

Re: cpyfrmimpf



fixed

Why are you messing with CPYFRMIMPF? Pretty much putting a gun to your head
and pulling the trigger. Better to just read the data directly using IFS
and parse. I could send you some examples.


On Tue, Jan 8, 2013 at 4:50 PM, A Paul <amandapaul1@xxxxxxxxxxx> wrote:

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 flat
file, I'm trying to load this file into externally described physical
file.

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,"-15,879.47"

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.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact