Joel,
That would work. Part of the issue is that a couple of programs that I
want to do this to are called from many different CL's so creating a
intermediate file, either with OPNQRYF or SQL involves putting that code
in a lot of places. Maybe I will create a wrapper CL to do this and
then change all the calling CL's to call the wrapper program, that would
isolate the code to only one place in case it needs to be changed.
Scott
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Monday, April 22, 2013 1:10 PM
To: MIDRANGE-L@xxxxxxxxxxxx
Subject: 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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
________________________________________________________________________
This inbound email has been scanned for all viruses by the MessageLabs
SkyScan
service.
________________________________________________________________________
______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit
http://www.symanteccloud.com
______________________________________________________________________
midrange.com
