Extended Screens › Extended Table Derivation Screen—XDER

Extended Table Derivation Screen—XDER

Description

The Extended Table Derivation screen allows you to perform derivation functions beyond the simple derivation possible on the Table Definition screen. Using the Extended Table Derivation screen, you can:

• Derive a view from more than two data tables
• Derive a view from one or more records

The upper portion of the screen, which is used to identify the source tables, can be paged. Two source tables can be identified on each page; up to six sources can be used to derive a view.

The lower portion of the screen is used to specify join information. Every source table identified must be joined to at least one other source. The lower portion of the screen is not pageable; a maximum of five join operations can be specified.

Access

You can access the Extended Table Derivation screen in one of the following ways:

• Select the Extended Table Derivation function from the Extended Table Definition screen; you can select the function by pressing [PF2].
• Enter XDER in the screen name field of any other screen.

Sample Screen

                                            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
  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 Der
  DEPT-EMPLOYEE                    02     ST                                   01

Fields

Table Name

Displays the name of the table as specified on the Activity Selection screen.

[PF1] - Help

Invokes the Help screen.

Page: __ Of __

Displays the current screen page number and the total number of pages in the page set. Only the top portion of the screen can be paged. You can move to a particular page by specifying its page number, or page forward using [PF8] and backward using [PF7].

Derivation Source

Numbers each source table identified. The derivation source numbers are used to specify join information. Two source tables can be identified on each page. ASF numbers each source when it accepts the derivation information.

Source Name

Specifies the name of a table, either a stored data table or a non-SQL defined record.

Source Owner

Enter the name of the table owner if the source table is a stored data table. You do not need to complete this field if you own the source table.

Subschema

If the source table is a non-SQL defined record, enter the name of the subschema in which the record is defined. The subschema must be generated from the system-supplied schema, IDMSR. The source record must be defined in the system-supplied schema, IDMSR.

Schema

Displays the schema name IDMSR when a non-SQL defined record has been identified as the source table.

Version

Displays the version number of the schema (1).

Note: If the source table is a stored data table, complete the Source Name and Source Owner fields under each Derivation Source heading. If the source table is a non-SQL defined record, complete the Source Name and Subschema fields. The Schema and Version fields will be completed automatically.

Join Information

When all the source tables have been identified, use the following fields to specify join information. Five join conditions can be specified; each source table identified must participate in at least one join operation.

Column #1/Set

Enter the name of the column or set used in the join operation, as follows:

• If you are joining two stored data tables, enter the name of a column in one table that is related to a column in the other table.
• If you are joining two non-SQL defined records, enter the name of the set that connects the two source tables.

Of Der

Enter the Derivation Source number supplied for the source table, as follows:

• If you are joining two stored data tables, enter the number of the source table to which the column entered in the Column#1/Set field belongs.
• If you are joining two non-SQL defined records, enter the number of the owner of the set specified in the Column#1/Set field.

Oper

Enter the operator that defines the condition for joining the two source tables, as follows:

• If you are joining two stored data tables, you will typically enter EQ in this field to join Column #1 and Column #2 on the basis of equal values. The following operators can also be specified to join two source records:
  • NE specifies that the sources are to be joined when Column #1 is not equal to Column #2.
  • GT specifies that the sources are to be joined when Column #1 is greater than Column #2.
  • LT specifies that the sources are to be joined when Column #1 is less than Column #2.
  • GE specifies that the sources are to be joined when Column #1 is greater than or equal to Column #2.
  • LE specifies that the sources are to be joined when Column #1 is less than or equal to Column #2.
• If you are joining two non-SQL defined records, enter ST to specify that source table #1 is the owner of source table #2. ST is valid only for non-SQL defined records joined on the basis of a predefined set relationship. The ST operator indicates that the entry in the Column #1/Set field is a set name.

Column #2

Enter the join column in another source table that is related to Column #1. Note that you do not specify Column #2 when joining tables on a set relationship.

Of Der

Enter the Derivation Source number of the source table to which the column entered as Column #2 belongs. If you are joining tables based on a set relationship, enter the number of the member of the set.