Training

Do you often encounter the many-to-many join situation when writing a query or report that needs to combine information from two detail files in one result set? This situation is a common one, particularly for JD Edwards (now Oracle) users who are looking for sales information from both the F4211 and the F42119. One way to avoid many-to-many joins is to use a union. A union enables you to write two select statements, each targeting a necessary detail file, and then combine the results into a single set.

There are four requirements for creating joins:

• Each select statement must have the same number of columns
• The columns must be type compatible
• Only the last select statement should contain a sort
• UNION will remove duplicates from the result set, whereas UNION ALL does not remove duplicates. Using the union can lengthen reports, so be sure that it doesn't remove non-duplicate data.

To create a union:

• Create the two queries separately and test them
• Copy the second query’s select statement to the clipboard
• Open first query’s select statement and type UNION or UNION ALL after the last piece of the select statement, usually the WHERE or GROUP BY clause
• Paste the copied select after the UNION or UNION ALL
• Verify that there is only one ORDER BY clause and that it is part of the last select statement
• Test the combined select statements

The following example is a union bringing together current sales from SCSAMPLE65.ORDERS and historical sales from SCDB1.ORDMAST: