RE: SQL index over field from another file

Chuck,

I am ok with recompiling the programs, I want to leave it with an Input
Primary file. The program uses the cycle with many level breaks and I
am trying to leave all that intact without changing. The ARBAL file
which is defined as IP uses the key, which is the customer number, just
for being able to do level break processing. I figure if I feed it a
sequence of Customer Name, Customer Number then the level break
processing will function the same as it does now.

So, I am just going to write a wrapper CL to do the OPNQRYF and then
call the program. Then I can change all calling programs to call the
wrapper.

Since I couldn't figure a simpler way I just thought I would throw out
to the list just in case I was missing something.


I agree that outer join doesn't make sense unless something is wrong but
I thought it would be a safety net in case somebody figured out how to
get a AR balance out there without a customer. The old version of the
programs would have showed it so I didn't want my program to 'hide' it.
If anything ever showed, I would have to track down why and fix it.

Thanks,

Scott


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, April 22, 2013 2:14 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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