When I run the query below (with the sub-select) it gives me the right answer, but it runs quite slowly. Without the sub-select line, it also runs fine (without the “aTotal” column being created of course), and completes at least 6 times faster … a noticeable difference.

select A.Field1, B.Field2, C.Field3, D.Field4, ;
       (select SUM(E.Field5)from EFile E where E.kEKey = B.kBKey) as aTotal ;
  from      AFile A  ;
       join BFile B on B.kBKey1 = A.kAKey ;
  left join CFile C on B.kBkey2 = C.kCkey ;
  left join DFile D on B.kBKey3 = D.kDKey ;
 where A.AKey = ?gkKey       ;
 order by C.Field3, D.Field4

All the tables above are quite large, except “EFile”, which is found only in the sub-select. As I develop the system, the EFile table is only a handful of records long right now, although it will be growing in the future. All of the Key fields above are indexed. Typical resulting cursor is about 2 to 8 rows. The real code is more complex, but conceptually the same.

Can someone suggest an alternate approach to using the sub-query (at least in the way that I’ve used it), so that I don’t take such a speed hit in adding the SUM(E.Field5) information.

Physically, the sub-select is totaling the number of a given line item that has been invoiced across all invoices (part shipments) for a particular order for a particular customer.

Bob