RE: SQL index over field from another file

You mention "create index" which IMO cannot be done with EITHER opnqryf or SQL (unless an intermediate work file is created for the join).

If you are open to drop the "index w/o workfile" requirement (if that is a req), I would think that you could mimic opnqryf with SQL and create a QTEMP work-file joining the ARBAL with custmast with an ORDERBY cust_name.

That should work pretty much identically to the OPNQRYF solution IMO.

If you do a level check *no on the file, you should be able to get by without a pgm recompile which is nice.


Something like:

create table qtemp/ARBAL as
(select
a.* from ARBAL a
join CUSTMAST b on a.CustNbr = b.CustNbr
order by CustomerName

) with data



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Scott Mildenberger
Sent: Monday, April 22, 2013 12:29 PM
To: MIDRANGE-L@xxxxxxxxxxxx
Subject: SQL index over field from another file

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)



Thanks,



Scott Mildenberger

Programmer/Analyst

Davis Transport Inc.

Missoula, MT

406-728-5510 x128