Extended Functions of ASF › Deriving Views from Various Sources

Deriving Views from Various Sources

Using the Table Definition screen, you can derive a view from one or two existing data tables. With the Extended Table Derivation screen, you can:

• Derive a view from up to six sources
• Derive a view from non-SQL defined records

A sample Extended Table Derivation screen is shown below.

                                      CA
  XDER   CA - Automatic System Facility           *Extended Table Derivation*
  DC560802 ADD/MODIFY DERIVATIONS
  Table Name: EMPLOYEES AND DEPARTMENTS
                                                  _ PF1 - Help   Page:  1  of  1

                           Derivation Source 01            1
  Source Name.: EMPLOYEE                                   
  Source Owner:                                            
                                                           
  Subschema...: EMPSS01     Schema: IDMSR       Version: 1 
                                                           
                           Derivation Source 02            
  Source Name.: DEPARTMENT                                 
  Source Owner:                                            
                                                           
  Subschema...: EMPSS01     Schema: IDMSR       Version: 1 


           Column #1/Set       Of Der   Oper               Column #2       Of Der1
  DEPT-EMPLOYEE2                   023    ST4                                  05

• 1 Identifying the source tables

Joining the tables on an owner-member set relationship

• 2 Set name
• 3 Number of set owner
• 4 Join operator
• 5 Number of set member

The Extended Table Derivation screen is divided into two sections, as follows:

• The top half of the screen is used to identify source tables. This portion of the screen can be paged. A total of six sources can be identified for derivation.
• The lower half of the screen is used to specify join information.

To Identify Sources

To identify sources for table derivation enter the appropriate information for each source, depending on its type:

• If the source is a stored record created by coding schema DDL, enter the Source Name and Subschema.
• If the source is a data table created through ASF, CA-ICMS, CA OLQ, or CA Culprit, enter the Source Name and Source Owner. The source table itself may be a derived view, so long as the source table is not derived from non-SQL defined records.

Note: Data tables and non-SQL defined records cannot be joined to each other. All derivation sources must be of the same type.

Page the top half of the screen forward ([PF8]) to identify additional sources for derivation. ASF numbers each source consecutively. The source table numbers are used to specify join criteria.

Specify Join Criteria

When all the source tables have been identified, you can specify join criteria for a view in the lower half of the screen:

• Join data tables based on common column values
• Join non-SQL defined records on set relationships

When entering join information, you can page the top half of the screen to reference the numbers assigned to the source tables. Since the join criteria apply to the entire view, it does not matter which sources are displayed in the top half of the screen at any given time.

Note: When deriving a view from more than one source, every derivation source identified must participate in at least one join operation.

Use the Extended Table Derivation to:

• Derive views from data tables
• Derive views from non-SQL defined records

Each of these topics is discussed separately on the following pages.