Show TOC

Defining Table Joins without the Help of GraphicsLocate this document in the navigation structure

Prerequisites

You have made the following settings:

  1. On the InfoSet: Initial Screen, menu path Start of the navigation path Settings Next navigation step Settings End of the navigation path, you have deselected the Graphical Join Definition checkbox.

  2. In the InfoSet: Title and Database dialog box, you have chosen the Table Join (Using Table) data source.

  3. You have specified the name of a table, and chosen OK.

Context

You can also define table joins without using graphics to help you.

For more information, see Defining Table Joins with the Help of Graphics.

Procedure


  1. You are in the InfoSet: Tables in Join screen.

    On the left-hand side of the screen, specify all the tables that you want to include in a join.

    Note

    If you want to include a table in a join more than once, use the Alias Tables function. Determine an alias name for the table. You can include the table in your join under both its original name and its alias.

    For more information, see Assigning Additional Tables.

  2. Determine the join type between the pairs of tables. Possible join types are:

  3. You must define Link conditions for each pair of tables in the join. There are two steps to this process:

    1. You choose the Define Condition function to determine between which tables you need to define a link condition.

      • If you join exactly two tables, these are included in the list of table pairs in the right-hand area of the screen.

      • If you join more than two tables, the system displays a message in the status bar asking you to select only two tables. Set the corresponding marker. Set the appropriate indicators. Choose the Define Condition option once more.

    2. In the right-hand side of the screen, choose the Specify Condition function for the pair of tables that you want to join. This determines exactly how the tables are joined.

      1. If this is the first time you have used the Specify Condition function with a pair of tables, the system proposes the standard default settings for the condition. These default settings are based on the foreign key relationships that are stored in the data dictionary or on the key fields of the tables in the join.

      2. The system displays the InfoSet: Link Conditions.

        The fields for each of the tables are displayed in two field lists, along with their technical names and long texts.

        You can navigate independently in both of these field lists:

        First Page, Previous Page, Next Page, Last Page.

      3. Determine the join conditions for the table fields.

        A table join can have any number of different conditions. In the result set, a join condition is accepted only if the connected fields have the same data type (including length) in the data dictionary (similarity relationship).

        Note

        Left-outer joins and right-outer joins are the exception to this rule.

        Click on the Field Documentation function to display information on the technical definition of a field.

        There are separate search functions for each table to help you find suitable fields:

        Find Left/Right, Find Next Left/Right.

        You can search according to:

        • text (technical names of fields and long texts)

        • Domain

        • dictionary type

        If you use the similarity relationship to join two fields, specify one and the same index in the input field that is assigned to one of the fields. An index consists of one or more of a number of different characters. We recommend you use numerical indexes between 00 and 99. The index has the technical purpose of ensuring that assignments are unique.

        If the system has been able to join the fields successfully, the fields are displayed in the same row at the top of the field list, and joined with a symbol.

        If you want to remove a join condition, proceed as follows:

        Place the cursor over one of the fields in the join that you want to remove.

        Choose the Remove Relationship function.

        You have specified the join conditions for a table pair. Choose Back. You get to the Tables in Join screen.

  4. Click on the Back function to get to the InfoSet: Change screen.

    You can change the table join and the join conditions at any time. Choose Join. You return to the Tables in Join screen.

    For more information, see Defining Table Joins with the Help of Graphics.

Results

The result of a table join is again a (flat) table. Hierarchical relationships are not evaluated.

Therefore all tables in the second table subtree are listed at the same level on the InfoSet: Change screen. Additional tables, additional fields, and additional structures are always connected to the first table in the table join. (In the WHERE conditions of connected additional tables or in the coding of additional fields, you can, however, access all the fields that are included in the table join). There is only the code for record processing.

For more information, see Special Features.