Retrieving Data
Most ABAP programs that you develop will need to work with data from the SAP database (for example, master data or transactional data). Data retrieval is a complex topic. For example, database accesses can have a huge impact on the performance of an ABAP program.
When retrieving data, the first thing you must do is identify the database table that stores the data.
The topic of data retrieval is not covered in depth in this course. Instead, this course provides a general idea of the syntax you can use and the things that you need to consider. This lesson looks at simple data retrieval of single records from the database.
When retrieving data, the first thing you must do is identify the database table that stores the data. The figure, Database Tables, illustrates the structure of a database table.
Database Table Definitions
ADT provides a dedicated editor for database table definitions.
Let's look at the different parts of the table definition.
The main part of the definition consists of a DEFINE TABLE statement with the name of the table. This is followed by the list of table fields in a pair of curly brackets ( { , } ); each field with a type that is described by a data element. A subset of fields at the beginning of the list defines the key, which identifies each record uniquely.
Several additional code lines before the DEFINE TABLE statement specify additional properties of the database table, among them a label.
Data Preview for Database Tables
You can use the Data Preview tool to display and analyze the content of a database table. To open the Data Preview for a given table, right-click anywhere in the table definition and choose Open With → Data Preview. Alternatively, place the cursor anywhere in the database table definition and select F8 on your keyboard.
ABAP SQL Architecture
In the past, SAP systems had to support a range of database platforms, and each platform had a slightly different implementation of the SQL standard. This meant that each platform needed slightly different commands to achieve a particular task. To avoid the ABAP code being database-specific, SAP invented ABAP SQL – or Open SQL as it was called originally.
Open SQL or ABAP SQL is an abstract set of SQL commands implemented at ABAP level and integrated into the ABAP language. At runtime, ABAP SQL is translated into a variant of SQL that the database understands. This variant is called Native SQL to distinguish it from the database independent ABAP SQL. The translation from ABAP SQL to Native SQL takes place in the database interface, a component of the ABAP system that consists of a general part and a database-specific library.
Even though today many ABAP-based SAP products only support SAP HANA as DBMS, SAP has still retained the concept of ABAP SQL and the database interface. This is because of the following reasons:
- Architecture compatibility
- ABAP SQL and the database interface are an integral part of the system architecture
- Code compatibility
- Code
ABAP SQL coding from previous SAP products (including customer-specific development) should run free of side-effects in the modern, SAP HANA-only ABAP environments.
Built-in features of the database interface are as follows: the database interface does not just translate statements; it is also responsible for ABAP specific tasks like, for example, automatic client-handling.
Reading a Single Record from the Database
To read data from the database, you use the SELECT statement.
When you write a SELECT statement in ABAP SQL, the syntax check compares what you have written with the definition of the tables and views. If you try to address tables, views, or fields that do not exist, a syntax error occurs.
The basic syntax of the SELECT statement contains several sections, called clauses, and always follows the pattern in the figure, The SELECT Statement in ABAP. The most important clauses of a SELECT statement are as follows:
- FROM
In the FROM clause of the SELECT statement, you specify the data source from which you want to read. This can be either be a database table or a view. Special SQL techniques allow you to combine data from multiple sources in the same SELECT statement.
- FIELDS
In the FIELDS clause of the SELECT statement, you list the columns of the database table that you want to read. The columns in the list must be separated by commas. If you want to read the entire table line, you can specify FIELDS * instead of a column list. Be aware, however, that this can cause the database considerably more work than just reading the columns you need.
- WHERE
In the WHERE clause, you can specify a condition that describes which rows of the table will be read. For example, the condition WHERE carrier_id = 'LH' means that only those rows will be read (in which the column CARRIER_D contains the value LH).
The WHERE clause can contain multiple conditions linked with the AND and OR operators. For example, WHERE carrier_id = 'LH' and connection_id = '0400' would return the data of flight connection LH 0400. You can also negate conditions using NOT. The WHERE clause is the only clause that is optional. Be aware, however, that without a WHERE clause you read all data from the table, or if the table has a client field – all data that belongs to the logon client of the user. SELECTs without a WHERE clause can cause serious performance problems and should be avoided.
- INTO
The INTO clause specifies the variable or variables in the ABAP program into which the data should be placed. This is normally a structure, or an internal table, and should ideally have the same sequence of components as the column list in the FIELDS clause.
The SELECT SINGLE Statement
You can use the SELECT SINGLE statement to read a single record from the database table.
With a SELECT SINGLE, the full business key of the table is often provided in the WHERE clause. This ensures that the single unique record that is required is fetched from the database.
In the figure, The SELECT SINGLE Statement, the developer defined a structure (ls_airport) that has the same components as the database table (/dmo/airport).
The SELECT SINGLE statement reads four fields from that database table: airport_id, name, city and country.
The INTO clause specifies the structure in which the returned data will be stored. INTO CORRESPONDING FIELDS OF tells the database interface to put the data into the fields of the structure (ls_airport) that have the same names as the fields retrieved from the database table (/dmo/airport).
If the system finds a suitable record, the system variable sy-subrc returns the value 0. If this occurs, the program outputs the data that was retrieved.
