Dan,
I tried that and it creates the view but it isn't in order by CUSNAME.
Scott
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan Kimmel
Sent: Monday, April 22, 2013 1:11 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL index over field from another file
I have not tried this, but apparently you can create a view with an
ordered subselect. Something like this is supposed to work:
CREATE VIEW ORDERED_CUSTOMER (ARBAL_CUST, ARBAL_BAL) AS SELECT
joined.ARBAL_CUST, joined.ARBAL_BAL from (SELECT ARBAL.CUST as
ARBAL_CUST, ARBAL.BAL as ARBAL_BAL, (SELECT CUSNAME from CUST where
ARBAL.CUST= CUST.CUST) as CUSNAME from ARBAL order by CUSNAME) AS
joined;
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Monday, April 22, 2013 12:55 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL index over field from another file
I don't believe this can be done the way you're wanting to.
The way I see it, you don't want to:
- use OPNQRYF (I'm cool with that).
- don't want to change the logic from RLA to imbedded sql But you cannot
create an index over two tables.
And a view cannot have an order by.
Back in the bad old days, third normal form might have been tossed out
the window and a column added which either had the customer name, or the
first so many characters thereof. On an existing table an effort may
have been undertaken to discover a column that was unused and repurpose
that. Then an add or change trigger would propagate this repurposed
column with the name (or abbreviation). Then build your index on that .
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
From: "Scott Mildenberger" <SMildenberger@xxxxxxxxxxxxxxxxxx>
To: <MIDRANGE-L@xxxxxxxxxxxx>,
Date: 04/22/2013 01:30 PM
Subject: SQL index over field from another file
Sent by: midrange-l-bounces@xxxxxxxxxxxx
I would like to change some existing programs that use our A/R balance
(arbal) as an Input Primary file to now be sequenced by the customer
name which is in the Customer Master (custmast). I can accomplish this
with an OPNQRYF but would like to create an SQL index or view or
combination that I could change the program to use directly. My goal at
this point is to be able to just change the F spec in the program to a
different file name and recompile.
I have the following SQL statement which gives me the fields in file
arbal ordered by a field in file custmast.
select arbal.* from arbal left outer join custmast on abcustomer =
tucust order by tuname
I cannot figure out a way to get an index created that would present the
records in this order. Any ideas if this is possible?
Here is the OPNQRYF that accomplishes the task if that is useful:
OPNQRYF FILE((ARBAL) (CUSTMAST))
FORMAT(ARBAL)
KEYFLD((TUNAME))
JFLD((ARBAL/ABCUSTOMER CUSTMAST/TUCUST *EQ))
JORDER(*FILE)
Thanks,
Scott Mildenberger
Programmer/Analyst
Davis Transport Inc.
Missoula, MT
406-728-5510 x128
midrange.com
