CREATE TABLE QTEMP/TEMP (
ORDER# CHAR ( 5) NOT NULL WITH DEFAULT,
FILLER1 CHAR ( 1) NOT NULL WITH DEFAULT,
CODE CHAR ( 1) NOT NULL WITH DEFAULT,
FILLER2 CHAR (7 ) NOT NULL WITH DEFAULT, IND CHAR (1) not null with
DEFAULT,
FILLER3 CHAR ( 1) NOT NULL WITH DEFAULT,
SDESC CHAR ( 44) NOT NULL WITH DEFAULT,
FILLER4 CHAR ( 17) NOT NULL WITH DEFAULT,
LDESC CHAR ( 100) NOT NULL WITH DEFAULT)
CREATE view QTEMP/TEMPx as (
select code, sdesc from qtemp/temp
where ind='1'
)
FTP the file in & done
Thanks,
Tommy Holden
-----Original Message-----
From: WDSCI-L [mailto:wdsci-l-bounces@xxxxxxxxxxxx] On Behalf Of Duane Scott
Sent: Tuesday, August 30, 2016 2:14 PM
To: Rational Developer for IBM i / Websphere Development Studio Client for System i & iSeries <wdsci-l@xxxxxxxxxxxx>
Subject: Re: [WDSCI-L] Making the most, or over-thinking it - SQL for uploads
FWIT - the row layout is as follows
Position Length Contents
1 5 Order number, right justified, zero filled.
6 1 Blank
7 7 Code.
14 1 Blank
15 1 0 if the code is a "header" - 1 if the code is detail
16 1 Blank
17 60 Short description
77 1 Blank
78 To end Long description
I needed only the Code (7:7) and the Short Description (17:60) and only those with a 1 in position 15.
There are 70k records in THIS file, which probably will grow by a couple of thousand each year.
There are similarly layed out files that need to be "imported" in similar fashion.
Simplicity is the concern. To me, simple is a single statement to insert data. SQL works well for that (for me) IF conditions are correct.
Besides, I am not a big fan of CPYFRMIMPF or Data Transfer unless the files are already formatted in CSV.
Code page (IMHO) should not be an issue, because it's not when I can see and read the data as a text file when opening under an editor. But that's where I become weaker on understanding the world outside of IBM i.
Drag and drop is a great part of RDi. When I can drag a PC-based text file to a Library on the IBM i, and a wizard pops up leading me to tell it where and how to plop it into a file, then RDi'll be greater. I just don't want to re-invent a wheel here. Besides, my choice for programming it would be RPG or Visual Basic and I don' t know if that's the best way to go.
duane
-----Original Message-----
From: WDSCI-L [mailto:wdsci-l-bounces@xxxxxxxxxxxx] On Behalf Of Booth Martin
Sent: Tuesday, August 30, 2016 2:53 PM
To: Rational Developer for IBM i / Websphere Development Studio Client for System i & iSeries <wdsci-l@xxxxxxxxxxxx>
Subject: Re: [WDSCI-L] Making the most, or over-thinking it - SQL for uploads
What are attributes of the .txt file that might lead us in a direction?
Are the files always large, small, unpredictable? Are there ever code page issues? While effectiveness is always the major concern, is efficiency of any concern here?
RDi does offer drag n drop to the IFS, if that makes a difference.
On 8/30/2016 1:16 PM, Duane Scott wrote:
Greg,
You are probably correct in this being overkill. I tend to do that, especially in the beginning of learning how to do something.
However, using your method is a bit of overkill too, if seen in the light of having to deal with all the data from a file for which only a small portion is desired. And, it's again, then a two-step process where it might not be otherwise had SQL been able to open and read a PC based .txt file.
I use CPYFRMIMPF in many programs that are part of our nightly routines. And being a long-time program, it's welcome in many situations.
duane
--
This is the Rational Developer for IBM i / Websphere Development Studio Client for System i & iSeries (WDSCI-L) mailing list To post a message email: WDSCI-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/wdsci-l.
________________________________
________________________________
CONFIDENTIALITY NOTICE: This electronic message transmission is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. If you have received this transmission, but are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this information is strictly prohibited. If you have received this e-mail in error, please contact NALC Health Benefit Plan at 703-729-4677 and delete and destroy the original message and all copies.
--
This is the Rational Developer for IBM i / Websphere Development Studio Client for System i & iSeries (WDSCI-L) mailing list To post a message email: WDSCI-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/wdsci-l
or email: WDSCI-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/wdsci-l.
midrange.com
