To define an additional table for a data source table, proceed as follows:
Double-click on a table that you want to assign to an additional table and choose the function Extras.
Choose the function Create (in the lower right half of the screen).
A dialog box appears where you must first choose to define either an additional table, an additional field, an additional structure or code.
The Change function then automatically displays the appropriate maintenance screen. It can also be triggered by a simple double-click.
Choose Additional table and enter a name for the table. You can use all table names found in the ABAP Dictionary or those already declared as alias tables for your InfoSet (see Multiple Additional Table Assignments below).
The system then creates the corresponding SELECT statement in another dialog box, if you selected a logical database as a data source. It then proposes a range of the assignment of this SELECT statement in the code section of the generated query report for the GET event of the logical database table. This sequence is important where, for example, you use a table field that derives its value from a SELECT statement as a key in the SELECT statement of another table assigned to the same logical database table.
The system shows you the key fields for the specified table. The system often also gives you a proposal as to how the key field could be filled. For example, it always proposes SY-LANGU as the language key. However, such default values often serve no useful purpose and you must specify values for the key field yourself.
This option internal buffer can be used to improve the performance of your query. If you choose this option, then all queries that use this additional table will save the records they read from it in an internal table. If a record from the table is needed, the system first checks to see if the record already exists in the internal table. If the record does not exist in the internal table, then a SELECT SINGLE statement is used to read the record from the database and insert it into the internal table. Internal buffering prevents additional table records from being read more than once each time a query is executed.
Internal buffer should only be used when you expect an additional table record to be read numerous times during query processing. Otherwise you should avoid using the internal buffer option because the internal table takes up memory space. In addition, with very large tables with numerous fields you should check to see if the time saved by using the internal table justifies the amount of memory space needed to store it.
In logical database F1S, which is used for examples in this handbook, two fields exist in the table SPFLI for departure and arrival airport. Both fields contain a key for the table SAIRPORT, which contains the long texts for airports. In a case like this, you should buffer the additional table internally, since it is safe to assume that records in SAIRPORT will be read multiple times.
You must now specify values for all key fields that do not contain a default value. You can, of course, overwrite the defaults.
For this purpose, you can use fields (for example SY-LANGU), number literals (for example 34), text literals (for example 'D') or parameters.
You should check carefully that any fields you assign to key fields contain valid values when the additional table is read. The system helps you to do this by allowing the following fields:
Parameters (see Creating Parameters)
Data from the DATA code (see Further Code).
Fields of the logical database table, to which the additional table is assigned, and fields from hierarchically superior tables of the logical database. In the case of the latter, any additional fields and additional table fields assigned there are also allowed.
If, for example, you assign the additional table to the table SFLIGHT of the logical database F1S, you can use fields from the tables SPFLI and SFLIGHT. It is also possible to access the fields of additional tables as well as additional fields assigned to the table SPFLI.
Fields of another linked additional table assigned to the same logical database table.
In this case, you must ensure that SAP Query reads the additional tables in the correct sequence.
Additional fields assigned to the same logical database table.
Here, you must ensure that any calculations involving additional fields are performed before accessing the additional table.
You can choose Check additional table to check your SELECT statement for syntax errors. Otherwise it can only be corrected when you use the functions Check and Generate for the InfoSet area as a whole.
Whenever you are on the screen for editing additional tables, you can change the following settings:
the sequence of the SELECT within the processing of data for the logical database table.
the Buffer internally option.
the key field setting.
However, you cannot change the assignment to a logical database table. To assign an additional table to a different logical database table, you must delete if first and then assign it again.
Multiple Additional Table Assignments (Alias Tables)
The procedure described above allows you link a table to an InfoSet as an additional table once only. Whenever you attempt to link the table a second time as an additional table you receive an error message informing you that the table may only be used once. The following example demonstrates, however, that multiple linking of a table may be useful.
In logical database F1S, which is used for examples in this handbook, two fields exist in the table SPFLI for departure and arrival airport. Both fields contain a key for the table SAIRPORT, which contains the long texts for airports. If table SAIRPORT could be linked to SPFLI as an additional table only once, then the long texts for only one of these two types of airport entries could be accessed. The long text for the other airport entry would have to be determined in another manner (using an additional field).
A similar problem occurs when a table that you want to link as an additional table belongs to a logical database. Here you once again receive an error message stating that this table may be used only once.
You can, however, use alias tables to allow you to link your table more than once.
You can use several different alias names for a table. The table can be referred to more then once using the different names.
To define an alias name for a table, proceed as follows:
Choose Insert Table.
A window appears displaying all existing alias names including the Dictionary table definitions to which the alias names refer. Alias names may not be repeated within an InfoSet. They must always refer directly to a Dictionary table definition and may not be identical to the name of any Dictionary definition.
The Alias Tables function also appears as a pushbutton in many dialog boxes.
In the lower left-hand corner of your screen, choose Create.
Another window appears where you can enter the alias name and the name of the assigned table from the Dictionary.
In order to delete an alias name, place your cursor on the appropriate entry in the list of alias names and choose Delete. You can delete only if the alias table is not used in the InfoSet.
In order to link a table as an additional table, within an InfoSet more than once, proceed as follows:
Link the table once using its original name.
Define an alias name for the table.
Link the table a second time as an additional table using this alias name.
Note that the fields from this table will now appear twice within the InfoSet with the same long texts and headers. Hence, when a table is linked multiple times, the long texts and headers of its fields must normally be changed whenever they are assigned to a single field group.