Re: SQL index over field from another file

[CRPence <CRPbottle@xxxxxxxxx>]

  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)