Re: Making the most, or over-thinking it - SQL for uploads

"WDSCI-L" <wdsci-l-bounces@xxxxxxxxxxxx> wrote on 08/30/2016 03:27:38 PM:

----- Message from Duane Scott <dscott@xxxxxxxxxxx> on Tue, 30 Aug
2016 19:14:29 +0000 -----

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.

. . .
. . .
. . .

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.

In terms of simplicity, the available options for SQL (a User-defined
table) probably won't qualify as that. It is doable, but I wouldn't say
it's simple. Also, Is RDi something that you want to use for a production
process? After all, it is Rational *Developer* for IBM i.

Besides, I am not a big fan of CPYFRMIMPF or Data Transfer unless
the files are already formatted in CSV.

You could upload the file directly from the PC using QNTC and the
following:

CPYFRMIMPF FROMSTMF('/qntc/youpc/sharedfolder/filename.txt') +
TOFILE(TESTLIB/MYFILE) MBROPT(*ADD) STMFLEN(nnnn) +
RCDDLM(*CRLF) DTAFMT(*FIXED) STRDLM(*NONE) +
RMVBLANK(*NONE) FLDDLM(*TAB) FLDDFNFILE(QTXTSRC ORDERIMPFD
)


The field definition file could be something simple like:
- --------------------------------------*/
- ----- Field Definition File */
- --------------------------------------*/
- Description: This field definition */
- file defines the import's file */
- (fromfile) field start and */
- end positions. */
- */
- --------------------------------------*/
-Field Str End NullInd
ORDRNO 1 5 0
DTLINDIC 15 15 0
DESC 17 76 0
*END

Isn't that really more simple than trying to squeeze it into SQL using a
platform that's intended to be a development tool, not a data manipulation
tool?

Just my 2 cents worth,
Michael Quigley
Computer Services
The Way International
www.TheWay.org