Creating joins between tables

Relationships between tables increase the power of queries. In QUERY, relationships, or joins, are created automatically when multiple tables are added to the query builder area, if a primary key link or a check table link exists between the tables.

Primary key links and check table links

A check table link exists when two tables are linked by foreign key fields. One table assigns some of its fields to be the primary keys of another table, called the check table.

A primary key join is possible when the primary keys in the tables have the same field name, and are of the same data type and field size. The primary is indicated by a key icon . Indexes are indicated by a blue inverted key .

Default joining of Primary and Index Keys

If a primary key and index key have matching data fields, the application creates automatic joins between the primary key and the index key. The tables must contain the same field name, field size, and be of the same data type.

NOTE: If primary keys are linked, index keys are not joined automatically.

User-created joins

In QUERY, you can create and remove joins if the administrator grants permission to do so. In addition to creating joins on regular tables, you can join cluster and pool tables, and views. You can also join a table to itself.

The administrator must grant permission for all joins. For more information, see Admin options.

NOTE: Automatic and user-created joins appear only on SAP systems where the function module is installed. For all other setups, only single-table downloads are possible.

To create a join between two tables

• Drag a field from one table and drop it into the other.

All downloads from joined tables are by an inner join--all matching records are downloaded.

Important: If you use multiple tables, they must be joined. If the tables that you query are not joined, the query will not run.

To join a table to itself

1. Double-click the table twice to add the table and its alias to query builder area. For example, if the table is MARA, its alias will have the name MARA1.
2. To create the join, drag a field from the table or alias to the alias or table.

All downloads from joined tables are by an inner join--all matching records are downloaded.

Left Outer Joins

You can create left outer joins in the QUERY builder. This would allow data to be returned from one table even though there are no matching records in the second table. Note that outer joins can degrade performance.

Recognizing left outer joins

You can recognize a left outer join in QUERY by the join line with an arrow pointing to the left table.

In case two tables are added with left outer joins, the joining arrow may not appear accurately. However, this does not affect the function of the join.

To convert to left outer joins

1. In QUERY builder, right-click on the joining link between the tables.
2. In the shortcut menu, click Convert to left outer Join.

   A warning messages displays stating that converting to left outer join may degrade the performance.

To remove or convert an outer join

1. Right-click the left join.
2. On the shortcut menu, click Remove or click Convert to outer join.

Conditions to create left outer joins

• From the outer joins only left outer join is supported.
• Maximum of 25 tables can be joined that can include inner and left outer joins.
• Only two tables can have left outer join between them in a query.
• The table on right of a left outer join cannot be a right table of any other join.
• For left outer join between two tables, no other type of join is allowed across any of their fields.
• Any field in the right table that has a left outer join cannot be used as selection.
• Any query with left outer join altered in previous version of QUERY may not generate accurate results.
• Following conditions are applicable for "Possible Joins" in case a left join exists:
  • All entries will conform to left join conditions.
  • Selecting some joins in the grid may disable other joins because of left outer join limitations.
  • Running a query with left outer join on version of Winshuttle Function Module with a version earlier than 10.2 displays an information message to abort the run.

Enabling left outer joins

Outer Join retains the data of the table on the left in addition to the mapped fields. You can enable the feature through the following steps:

1. On the Tools menu, click Options.
2. In the left pane, click Admin Defaults.
3. Select the Enable Left Outer Join check box.

To delete a join

• Right-click the join line that you want to delete, and then click Remove.

Joining with infosets and SAP queries

You can use only one Infoset/SAP query on query builder area at a time. Joining of more than one Infoset/SAP query with tables is not supported.