× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.