Joining tables using multiple FOR phrases

You can use multiple record phrases to join records from more than one table:

FOR EACH Customer NO-LOCK WHERE Customer.State = "NH", 
  EACH Order OF Customer NO-LOCK WHERE Order.ShipDate NE ? :
  DISPLAY Customer.Custnum Customer.Name Order.OrderNum Order.ShipDate.
END.

The following figure shows the result.

There are several things to note about this example:

• The AVM retrieves and joins the tables in the order you specify them in, in effect following your instructions from left to right. In this example, it starts through the set of all Customers where the State field = "NH". For the first record, it defines a set of Orders with the same CustNum value (represented by the OF syntax in this case). For each matching pair, it establishes that Customer record and its Order record and makes them available to all the rest of the statements in the block. Because there are typically multiple Orders for a Customer, the result is a one-to-many join, where the same Customer remains current for multiple iterations through the block, one for each of its Orders, as the output in the previous figure shows.
• If you change the sequence of the tables in the statement, the AVM might retrieve a very different set of records. For example, using the syntax FOR EACH Order WHERE ShipDate NE ?, EACH Customer OF Order, you would get a list of every Order in the Order table with a ShipDate, plus its (one) matching Customer record. Because there's just one Customer for each Order, this would result in a one-to-one join with no repeated records.
• The default join you get is called an inner join. In matching up Customers to their Orders, the AVM skips any Customer that has no Orders with a ShipDate because there is no matching pair of records. The alternative to this type of join, called an outer join, doesn't skip those Customers with no Orders but instead supplies unknown values from a dummy Order when no Order satisfies the criteria. ABL has an OUTER-JOIN keyword, but you can use it only when you define queries of the kind you've seen in DEFINE QUERY statements, not in a FOR EACH block. To get the same effect using FOR EACH blocks, you can nest multiple blocks, one to retrieve and display the Customer and another to retrieve and display its Orders. You did this back in the sample procedure in Introducing ABL. Generally this is more effective than constructing a query that might involve a one-to-many relationship anyway, because it avoids having duplicated data from the first table in the join.
• You can add a WHERE clause and/or a BY clause to each record phrase if you wish. You should always move each WHERE clause up as close to the front of the statement as possible to minimize the number of records retrieved. For example, the statement FOR EACH Customer, EACH Order OF Customer WHERE State = "NH" AND ShipDate NE ? would yield the same result but retrieve many more records in the process. It would go through the set of all Customers, retrieve each Order for each Customer, and then determine whether the State was "NH" and the ShipDate was not unknown. This code is very inefficient. The way ABL handles data retrieval is different from SQL, where the table selection is done at the beginning of a SELECT statement and the WHERE clause is after the list of tables. The SQL form depends on the presence of an optimizer that turns the statement into the most efficient retrieval possible. The advantage of ABL form is that you have greater control over exactly how the data is retrieved. But with this control comes the responsibility to construct your FOR statements intelligently.
• Because two records, Customer and Order, are scoped to the FOR block, you might need to qualify field names that appear in both of them. If you just write DISPLAY CustNum you get a syntax error when you try to run the procedure, as shown in the following figure.