Re: how to make a key on a view?

You can't make the index from THIS source member - you need a CREATE INDEX - go to the SQL Reference and look that up - I don't have the exact syntax in my head right now.

BTW, this doesn't look like the entire contents of that source - is there an extra bit about a constraint at the end? That could be a primary index.

You need to create a different source member of type TXT - actually I prefer type SQL, but it doesn't matter.

Then put the CREATE INDEX statement you have worked out into that source member.

Then use RUNSQLSTM, specifying this new member, and you will have your index.

One thing - try this command - DSPDBR PROJ_MKWN1/DTAWHS - this will tell you about any logical files over the table - once you have those names, do a DSPFD on them and see if they have key fields. Maybe you already have an appropriate index.

If you have a tool like DBU, you can get this same information faster.

HTH
Vern

On 1/30/2014 6:14 PM, Hoteltravelfundotcom wrote:

#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.