Join

To read records in a main table and then process related records in a transaction
table for each main table record, one solution is to make one "while select"
statement which loops over the records in the main table and another nested
"while select" statement which loops over the transaction records related to the
current record in the main table. The following is an example of a nested "while
select" statement

1 2 3 4 5 6 7 8

while select InventTable { while select InventTrans where InventTrans.itemId == inventTable.itemId { qty += inventTrans.qty; } }

To process 500 records in the main table, this approach would have 501 SQL
statements executed on the database.
Alternatively, making a single "while select" statement with a join clause reduces
the number of SQL statements to just 1.
The following example illustrates using a join clause (and fieldlists for extra
performance).

1 2 3 4 5 6

while select recId from inventTable join qty from inventTrans where inventTrans.itemId == inventTable.itemId { qty += inventTrans.qty; }

Best Regards,
Hossein Karimi