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