Re: SQL index over field from another file

On 22 Apr 2013 12:11, Dan Kimmel wrote:

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;

If that were able to effect proper ordering for the RPG OPEN, then so too would the following query utilizing the exact query offered by the OP, to give what could be a final resolution [if no keyed access path was necessary]:

create view ARBAL_ORDERED as
select
oj.?f1?, oj.?f2?, oj.?fn? /* explicitly name all of arbal.* */
from
( select arbal.*, tucust.tuname
from arbal
left outer join custmast
on abcustomer = tucust
order by tuname
) as oj /* Ordered_Join */


However, although a derived table expression can offer the capability to sort the intermediate results, the overall SELECT from the VIEW is still unordered, unless there is an ORDER BY. The outermost SELECT can not have an ORDER BY. AFaIK the OPEN of an SQL VIEW is implemented no different than an SQL OPEN for a SELECT on a VIEW; in either case, how the query engine implements its means to obtain the data is not influenced by the open being /native/ such that it would somehow honor the ORDER BY in the subselect as the effect of the overall /query/ SELECT which is the *unordered* result set for the OPEN.

Thus even if one were to experience many times in a row, an apparent effect matching the expectation of /ordered/ results, there is no assurance that the next request would effect that same order.

http://www.ibm.com/support/docview.wss?uid=nas18cbc115173a8dca286256c84006c6ead
"... When ordering of a cursor is not defined by an ORDER BY clause, the relative position of two rows is implementation-dependent..."

The native\RLA non-query open request has no means to request an ORDER BY. One would use OPNQRYF to effect that specification for use with RLA, or an ORDER BY specification on a SQL SELECT for use with SQL FETCH.