Create joins between tables - Automate_Studio - 20.3

Automate Studio with Connect User Guide

Product type
Software
Portfolio
Integrate
Product family
Automate
Product
Automate > Automate Studio
Version
20.3
Language
English
Product name
Automate Studio
Title
Automate Studio with Connect User Guide
Topic type
How Do I
Administration
Installation
Overview
First publish date
2018
ft:lastEdition
2024-05-06
ft:lastPublication
2024-05-06T08:28:10.436135

When you add tables to the workspace, Studio Query automatically creates joins between primary keys. If there are no primary keys, Query creates joins between index keys.

But the automatic joins might not link the specific fields you want to query. In that case, you can remove the joins and add new ones, either by dragging and dropping the field from one table to the other, or by using the Find Joining Table command. The fields should have the same name, size, and data type.

You can add joins between fields even if they do not have the same name, size or data type—but do this only if you are familiar with the underlying data in the tables. If the joins are non-indexed, the query will take longer.

Note:

Query does not automatically pad the fields of mismatched joins.

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 second instance of the table is referred to as the alias).

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

Find joins

  1. In the workspace, click a table.
  2. On the Workspace tab, click Find Joining Table.
  3. Click the drop-down menus to choose the tables that you want to join.

    Studio displays a list of direct joins.

  4. Select the check box for the join that you want, and click Apply Selected Joins.

Find intermediate joins

If Find Joining Table does not find any links between two tables, you can find a third table to connect with each of them (like a bridge). These connections are intermediate joins.

  1. In the Find Joins box, click Intermediate Joins.

    Studio displays a list of direct joins

  2. Select the check box for the join that you want, and click Apply Selected Joins.

Manually add joins

Create a join between two tables

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

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

Join a table to itself

  1. Double-click the Add to Workspace button to add the table and its alias to query builder area. For example, if the table is MARA, its alias is MARA1.
  2. To create the join, drag a field from the table or alias to the corresponding field in the table or alias.

All matching records are downloaded.

Left outer joins

You can use left outer joins to download data from one table even though there are no matching records in the second table. Note that left outer joins can slow the query and use more processing resources on the SAP system.

A left outer join has an arrow pointing to the left table.

Convert inner joins to left outer joins

  1. Right-click on the joining link between the tables.
  2. On the shortcut menu, click Convert to left outer join.

    A warning message appears to remind you that converting to a left outer join might slow the query.

To remove or convert a left outer join

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

Conditions to create left outer joins

  • From the outer joins only the left outer join is supported.
  • A query can contain only one left outer join.
  • A query can contain only 25 tables.
  • The table on right of a left outer join cannot be a right table of any other join.
  • For a 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 for selecting criteria.
  • Any query with a left outer join that was altered in previous version of Query might not generate accurate results.
  • When creating new inner joins in a query that already has a left outer join:
    • All entries will conform to left outer join conditions.
    • Selecting some joins in the grid may disable other joins because of left outer join limitations.
    • If the Automate Function Module predates version 10.2 and you run a query with a left outer join, the query will not run.

Delete a join

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

Mismatched joins

Automate Query allows joins between indexed and non-indexed fields, and also between fields of different character lengths. However, the information type, material, number, organization unit, and so on must be the same.

Joins between other mismatched fields might be allowed, but no data can be extracted. In such circumstances, Query displays a warning that no data can be downloaded through the join.

Primary key links and check table links

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 key is indicated by a yellow key icon primary key. Indexes are indicated by a blue inverted key index key.

Note:

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

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.