MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » January 2014

Re: how to make a key on a view?



fixed

#5 I can make the index when using the RUNSQLSTM? I have been away from the
i for a few years before this assignment since May. But they keep me mainly
on the reporting tool. No not because I can't code in RPG but because of
the workload.


On Thu, Jan 30, 2014 at 7:11 PM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

right not. this table was created by a consultant. For purpose of
recreating many of our reports from the i, to the reporting tool.
I do not know much about what exactly he did there. I would need to review
the RPG programs we have seen in that list.
but for my purposes, I wanted to use in the reporting tool but as most
optimized as possible.THis table does exist. I believe there is a nightly
refresh to it.


On Thu, Jan 30, 2014 at 6:50 PM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>wrote:

One thing - CHECK WHETHER DTAWHS EXISTS ALREADY IN THAT LIBRARY
(PROJ_MKWN1).

Please excuse the flaming - I'm not upset, just trying to make a very,
very strong point.

You probably do NOT want to wipe out existing data, right?

:)

On 1/30/2014 5:47 PM, Vernon Hamberg wrote:
You're jumping over things again - there are important gaps in what we
are hearing.

1. This member is NOT where the data resides. If that is what you think,
then disavow yourself of that notion instantly
2. This member is the source code for CREATING the table where the data
will reside.
a. That table is names DTAWHS and it is located in library
PROJ_MKWN1
3. I'm glad you keep asking questions, BTW, because it seems things that
are "obvious" to those who've been on the platform for awhile - that
they are not "obvious" to you - maybe you've been pushed into doing
these tasks, I think you've hinted at that.
4. So you will need to use this member to create the table, and the
command for that is RUNSQLSTM, where you specify this member, and out
comes an SQL table (also know as a PF).
5. You CAN make an index on that table, and if you are at a newer
release, there is more you can do, but I don't think you will need to.

So now I have to ask, how does the data get into DTAWHS? Or does that
table already exist and has been in production?

Good luck
Vern

On 1/30/2014 5:36 PM, Hoteltravelfundotcom wrote:
I wanted to index the invoice number as we are adding this processing.
THe
current report has 2 tables linked by part #. this new table has the
invoice amt. data which they wish to add to the report.


On Thu, Jan 30, 2014 at 6:32 PM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

It would be processed in the reporting tool, on the PC side.


On Thu, Jan 30, 2014 at 6:24 PM, Buck Calabro <kc2hiz@xxxxxxxxx>
wrote:

On 1/30/2014 5:58 PM, Hoteltravelfundotcom wrote:
the source for the DTAWHS is this
CREATE TABLE PROJ_MKWN1/DTAWHS (
CUSTOMER# CHAR ( 8),
NAME CHAR (40 ),
CITY CHAR (30 ),
STATE CHAR (3 ),
COUNTRY CHAR (3 ),
ZIP_CODE char (10 ),
ITEM# CHAR (15 ),
ITEM_TEXT CHAR (40 ),
ORDER# CHAR (8 ),
ORD_STATUS CHAR (2 ),
INVOICE# CHAR (8 ),
ORD_DATE NUMERIC (8 , 0),
INV_DATE numeric (8 , 0),
ORD_TYPE CHAR (3 ),
CUST_TYPE CHAR (3 ),
PROD_LINE CHAR (3 ),
SALES_REP CHAR (3 ),
WAREHOUSE CHAR (3 ),
Now we're getting somewhere.

The source you posted above is stored in the source file QDDSSRC in
member DTAWHS. This is probably processed via RUNSQLSTM. The fact
that
the member type is TXT is irrelevant to your question because the
member
type is only important to SEU, PDM and RDi (editors and compiler
front
ends).

On Thu, Jan 30, 2014 at 5:43 PM, Hoteltravelfundotcom <
hoteltravelfun@xxxxxxxxx> wrote:

here is the official info on this file. Note that in the DDS it is
has
a
TYPE of TXT, yet, on an Library level view, it has an attribute of
PF.



File . . . . . .
QDDSSRC

Library . . . . PROJ_MKWN1 Position to . . . .

Type options, press
Enter.

2=Edit 3=Copy 4=Delete 5=Display 6=Print
7=Rename

8=Display description 9=Save 13=Change text 14=Compile
15=Create
module...



Opt Member Type Text


DATAWHS_PT LF Part #
logical

DTAWHS TXT Create Table - SQL
Statement

SADTAW1F PF SA OutPut-Name/Add/Address (Ted
File)

SADTAW1FX PF SA OutPut-Name/Add/Address (Ted
File)

Once someone processed the source (CREATE TABLE), the database
manager
created a table - a PF. As you show below, that table is in fact a
PF
and it is called DATAWHS.


CMKWNDTW *PGM CLLE 90112 Clear
and Add
to
DTAW

MKWNDTW *PGM RPGLE 487424 SA
Detail File
Build

CSUBINV4 *PGM CLLE 102400 SA Ext
Rpt:
Inv &
Mis

SUBINV3 *PGM RPGLE 1200128 Make Inv
Dates
One-Ye

DATA_WHSV3 *FILE LF
65536

* DTAWHS *FILE PF
1210241024 *

DTAWHST *FILE PF
53248

QCLSRC *FILE PF 118784 CL Source
File

QDDSSRC *FILE PF 106496 DDS
Source
File

QRPGLESRC *FILE PF 405504 RPGLE
Source
File
Now for your question.

The table DATAWHS is not a view, it is a table in SQL parlance, or a
PF
to a native OS programmer. It looks as if someone has created at
least
one view (or LF) called DATA_WHSV3.

Do you want to access DATA_WHSV3 by a different key? Why? Is it
because there is select/omit criteria? Generally, one would do a
CREATE
VIEW over the base PF (table), not over another logical file. If you
already had a VIEW (and I don't see that here), then you'd do a
CREATE
INDEX to give the database manager some hints on how to optimise SQL
queries.

Let's assume for the moment that you had a VIEW with a key - how
would
you be accessing it? SQL SELECT (including embedded SQL)?
Query/400?
ODBC? RPG READE? OPNQRYF?
--buck
--
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