Extended Functions of ASF › Deriving Views from Various Sources › Deriving Views from Data Tables

Deriving Views from Data Tables

When deriving views from data tables, the Extended Table Derivation screen is an extension of the derivation portion of the Table Definition screen. It enables you to identify additional source tables and join criteria.

The first two source tables identified and the first join condition specified will be displayed on both screens.

Use the Extended Table Derivation screen to derive from additional tables.

Identify Sources

To identify tables for derivation, use the upper portion of the screen. Identify up to six source tables by completing the Source Name and Source Owner fields under each Derivation Source heading. Page forward as needed to identify additional source tables; a maximum of six source tables can be identified.

Specify Join Criteria

To specify join criteria, move the cursor to the lower portion of the screen. Use the following steps to enter each join condition:

• Type the name of the first join column under Column #1/Set.
• Specify the source table to which the column belongs by typing the derivation source number under Of Der.
• Enter the operator on which the data in the two join columns is to be joined. The operators are EQ (equal), NE (not equal), GT (greater than), LT (less than), GE (greater than or equal to), and LE (less than or equal to). .li Type the name of the second join column under Column #2.
• Specify the source table to which the column belongs by typing the derivation source number under Of Der.

Note: When specifying the derivation source number for a join column, be sure to verify the number assigned to the source table in the upper portion of the screen.

Join Considerations

When joining source tables, the following conditions apply:

• Joining on a not equal (NE) condition does not imply a no match condition. For example, if you join the CUSTOMER table to the ORDER table where the customer numbers are not equal, the view will not contain rows of customers without orders. It will contain rows of customers with every order but their own.
• Reflexive joins are allowed, enabling a table to be joined to itself. The table must be identified twice and referenced by two different derivation source numbers in the Of Der fields. It is recommended that the resulting view be used for display purposes only; updates are applied only to one of the rows in the source table rather than to the two rows displayed through the view.
• Additional conditions for joining two tables can be specified using the Extended Selection Definition screen.

A source table used to derive a view can be a view itself so long as the source was not non-SQL defined records.