OPNQRYF performance using joins with a large file

I have a sales history file (SLHSTDTP) that contains about 12,000,000
records per year. Some of the fields in this file are:

     WITEM        7,0        Item Number
     WCUST#     5,0        Customer Number
     WDATE       8L         Invoice Date - format is *MDY

The physical is keyed with WITEM being the 1st key field. There are logicals
with WCUST# and WDATE as the 1st key as well.

I am creating a generic sales reporting program that allows records to be
selected several different ways. Customers are grouped together by a Chain
Code. The user can select one or more Chains; or one or more Customer
Numbers. I take any Chain Codes and write all of the associated Customers
to a workfile. If Customer Numbers are entered, I write them to the workfile
instead. I now have a workfile (VELCUST) with a single field (CUSTNO 5,0)
that contains all of the Customer Numbers to select for the report.

To speed up my reporting, I will use an OPNQRYF statement to select only
those history records for the customers I will report on. The statement looks
like this:

     OPNQRYF  FILE(SLHSTDTP VELCUST)  FORMAT(SLHSTDTP)  +
                        JFLD((WCUST# CUSTNO))  OPTIMIZE(*FIRSTIO) +
                        OPTALLAP(*YES)  KEYFLD((WITEM)) +
                        QRYSLT('WDATE %RANGE('01/01/98' '08/31/98')')

The date range is also a variable, I show it as though it is hard-coded here!

This query is running very long. It also appears to be generating an 
intermediate
file. Is this because I'm using the join or is it because of something else? 
Although there are millions of records in the sales detail file, there is a 
total of
eight Customer Numbers in VELCUST and there is about 125,000 sales history
records for these eight customers. I have tried using the logical with Customer
Number as the key instead of the physical, but it has not helped. I have also
tried switching VELCUST and SLHSTDTP around on the FILE parameter, but
that has not helped either. I have also tried it with the OPTIMIZE and OPTALLAP
parameters and without.

If I go into Query/400, join SLHSTDTP with the Customer Master File and select
the same Customer Numbers from the Customer Master; the query runs fairly fast
(matter of a few minutes). My OPNQRYF statement is being cancelled after
running in excess of a half hour.

Does anyone have any suggestions?

#1 - SQL is not an option, the customer does not have it and is not interested 
in
puechasing it.

#2 - This is on V4R2. I'm promoting V4R3, but have not been able to get it sold 
yet.

Thanks, Joe Teff

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---