Re: Why Doesn't An Index Download In The Correct Order?

On 04 Apr 2013 22:02, w 4038 wrote:

I have two tables with the same data layout. TableA is inventory
from last month. TableB is the current inventory. The goal is to
put both tables side by side in a spreadsheet to compare last month
to the current month. Most of the rows are duplicates and exist
in both tables. Some rows are new items in the current month. Some
rows existed last month but no longer exist in the current month.
The following SQL statement creates a view and the side by side
comparison is perfect. (Thanks to B Hauser for the coalesce
suggestion)
create view qgpl/viewAB as
Select * from
(select *
from qgpl/tableA
full outer join qgpl/tableB
on f1file = f2file
order by coalesce(f1lib,f2lib)
, coalesce(f1file,f2file)
, coalesce(f1mbrname,f2mbrname)
) as t
But when I download the view using Client Access, the order is not
maintained. Does anyone know why?

FWiW the given text and the subject seem not to correlate. There would be no apparent INDEX being downloaded in the given scenario. The download is apparently of the VIEW that was created.?

The NTE subquery requested as its own query will produce the desired effect. The subquery in the VIEW is redundant as far as the optimizer is concerned, so for an /open/ of the VIEW, the query is, in effect, the result of just the subquery *without* the ORDER BY.

To force a query engine to effect a desired order, the /open/ of the VIEW must include an ordering request. Only a request to open the VIEW via the SQL Query Engine [SQE] or the Classic Query Engine [CQE] offers support to open the VIEW with any order specified. A /native/ OPEN of the VIEW has no capability to request order, so collation via that open method can not be predicted; e.g. open by an RPG, CL RCVF, FTP, etc.

IIRC a file transfer feature of the Client Access offers a query interface on which a ORDER BY clause can be specified; i.e. I believe that subquery could be requested *as* the transfer\download request, or more redundantly the transfer request could request a SELECT * FROM QGPL/VIEWAB with that same ORDER BY clause specification.