An outer join joins the two tables via a where clause in Oracle. In SQL Server either a where or from clause join can be generated. In DB2, only a from clause join can be generated. The ANSI standard ‘from clause’ join is recommended as multiple ‘where clause’ outer joins will return incorrect results under SQL Server. The outer join returns all rows in the master table regardless of whether or not they are found in the second table. So if the example above was executed with table A as the master table then 100 rows would be returned. 76 of those rows would have null values for the table B columns. When QAD Data Warehouse Designer builds up a where clause join it must place the outer join indicator next to the appropriate column. As this indicator goes on the subordinate table in Oracle, and the master in SQL Server, QAD Data Warehouse Designer needs to know which table is master and which subordinate. Select the join column from the master table first. In the example screen above the table 'load_product' has had its column chosen and the column for the table 'load_prod_subgroup' is currently being chosen. This will result in the 'load_product' table being defined as the master, as per the example statement as shown in the 'Where clause edit window' above. The results of this example select are that a row will be added containing product information regardless of whether or not a corresponding prod_subgroup entry exists.

As the join columns are selected the join statement is built up in the large edit window on the right hand side. Once all joins have been made the contents of this window can be changed if the join statement is not correct. For example the Oracle outer join indicator (+) could be removed if required.

Once satisfied with the join clause click the OK button to proceed to the next step. For Oracle this clause is the where clause that will be applied to the select statement of the cursor to allow the joining of the various tables. For SQL Server this clause will be either the where clause or a combined from and where clause depending on the option chosen. This clause can of course be edited in the procedure that is generated if not correct.

For SQL Server data warehouses you have the choice between 'Where' statement joins and ANSI standard joins. For DB2 data warehouses, only ANSI standard joins are available.

The example below shows the result of a SQL Server ANSI standard join which takes place in the 'From' statement.