I need to get some summary information for invoices and would like to find a neater way than the following. Rgw ibject is to get one line for each invoice, together with some parent information, and the sum() of the Amounts for its children. Following is a general query for all invoices, but sometimes I need to add a condition of WHERE CustomerID=???? and Invoices.Locked=.T., in which case, the cursors are wasting time retrieving too much data to start with.

Table structure is:

Customers-->ContracHeaders-->Contracts-->Invoices

Invoices can have as children zero to many from any of three tables -- InvoiceLines, Credits and Refunds

Each ID field is the primary key of the same named table

select InvoiceID,Amount FROM InvoiceLines ;
union ;
select InvoiceID,Amount FROM Credits ;
union ;
select InvoiceID,Amount FROM Refunds INTO CURSOR Cursor1

SELECT InvoiceID, $0.00 as Amount from invoices ;
WHERE invoiceID NOT in (SELECT invoiceID FROM Cursor1) INTO CURSOR Cursor2

SELECT * from Cursor1 UNION select * from Cursor2 INTO CURSOR Cursor3

SELECT invoices.*, ;
sum(Cursor3.Amount), ;
Contracts.ContractNumber, Contracts.Description as Contracts_Description, Contracts.ContractTotal, ;
ContractHeaders.ContractHeaderID, ;
Customers.CustomerID, Customers.LastName ;
from invoices ;
LEFT OUTER JOIN Cursor3 ON Cursor3.InvoiceID=Invoices.InvoiceID ;
LEFT OUTER JOIN Contracts ON Contracts.ContractID=Invoices.ContractID ;
LEFT OUTER JOIN ContractHeaders ON ContractHeaders.ContractHeaderID = Contracts.ContractHeaderID ;
left outer join Customers ON Customers.CustomerID=ContractHeaders.CustomerID ;
GROUP by Invoices.InvoiceID