Re: SQL index over field from another file

What is expressed as desired, "an index created that would present
the records in" the order of the column in the join secondary table is AFaIK, not something that is going to happen. Like Rob replied, the VIEW can not have an ORDER BY, and an INDEX can not order data in what is a dynamic join. If the goal is to find a way to avoid a recompile, I doubt that is possible without using a copy of the data as a result of the join; e.g. an MQT, GTT, or another form of a /temporary/ database file. I suspect that there will most likely only be some ability to minimize impacts to the programs, while accepting that a recompile is likely to be required.

If the existing programs just need the effect of that ordering with that format, without a need for a keyed access path definition, then I would just create a CLP with an OPNQRYF using the FORMAT to encapsulate the access to that file data. The OPNQRYF could reference the files and perform the join, or just query the VIEW which encapsulates the SELECT query shown; albeit to use the VIEW, the TUNAME would have to be included in the select-list, but that column would be omitted by the FORMAT() specification on the OPNQRYF.

If the ARBAL file is currently accessed using a keyed access path, then that new key requirement may effectively require those programs to be recompiled. If so: What is the key? Is the key used for positioning or other logic or output, or was its existence merely for ordering the rows? If that column is used for logic or output but not positioning, are there any columns from ARBAL that are unused in the program?

FWiW, the outer join seems odd. One might expect that a customer "master" file would be the parent, thus ensuring no possible ABCUSTOMER value in ARBAL which is not a TUCUST value in CUSTMAST.?

Regards, Chuck

On 22 Apr 2013 10:29, Scott Mildenberger wrote:

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)