The below query is very slow to execute (approx one row per second), as if it is reading the entire INF11M data file for each INF04M record. INF04001 contains the invoice number (1,6) and line number (7,3) and INF11001 is the invoice number which is the primary key of INF11M.
The only way we can see to join the headers (INF11M) to the lines (INF04M) is to use the SUBSTR function. Is there a better way?
SELECT * FROM 'INF04M' LEFT JOIN 'INF11M' INF11M ON INF11001=SUBSTR(INF04001,1,6)
Both files are KEYED files. INF11M has ~84,000 records.
FYI - same issue with something like:
LEFT JOIN 'INF09' ON INF31001+INF31002=INF09001