Re: Looking for file structure details

[Glenn Gundermann <glenn.gundermann@xxxxxxxxx>]

create index mylib.i_mytable
on mylib.mytable (field4 asc)
where field3 = 'X' ;


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (416) 675-9200 ext. 89224
Cell: (416) 317-3144

Please consider sponsoring me for the Friends For Life Bike Rally
<https://secure.e2rm.com/registrant/FundraisingPage.aspx?SID=5323935&LangPref=en-CA>
in support of the Toronto People with AIDS Foundation.


On 26 June 2015 at 12:49, John R. Smith, Jr. <smith5646midrange@xxxxxxxxx>
wrote:

> I'm still missing something in your example.
>
> Let's create a simple example and then you can provide the exact missing
> CREATE command.
>
> Table is created with the following.
>
> CREATE TABLE MYLIB/MYTABLE
> (
>   FIELD1 CHAR (10),
>   FIELD2 CHAR (10),
>   FIELD3 CHAR (10),
>   FIELD4 CHAR (10),
>   PRIMARY KEY (FIELD1, FIELD2)
> )
> RCDFMT MYTABLERCD
>
> How do I create a view/index/whatever with only records where FIELD3 = 'X'
> and Ordered by FIELD4.
>
>
> -----Original Message-----
> From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
> rob@xxxxxxxxx
> Sent: Friday, June 26, 2015 9:02 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: Looking for file structure details
>
> You CAN create an index with a WHERE clause.  IBM specifically made this to
> eliminate 99% of the arguments people had against SQL.
> Sample:
> Vendor creates table (let's call it IIM for an item master table).  Vendor
> puts no key on the table.  Table has an active record column (IID). Vendor
> creates a logical file in DDS like this
> R IPI100IM                  PFILE(IIM       )
> K IPROD
> S IID                       COMP(EQ 'IM')
> Of course the vendor is an a$$ who thinks their program logic will ensure
> there are no duplicate keys and whatnot.
>
> Several releases ago IBM came up with this in sql
>
> create index rob.iiml01
> on erplxf.iim (iprod)
> where iid='IM'
> rcdfmt IPI100IM add all columns
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
> to:  2505 Dekko Drive
>           Garrett, IN 46738
> Ship to:  Dock 108
>           6928N 400E
>           Kendallville, IN 46755
> http://www.dekko.com
>
> --
> 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.