SAP Learning Journey

Using Simple Data Retrieval

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Retrieve data from the database

Simple Data Retrieval

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.

Retrieve Data from a Database

Retrieving Data

Business Example

You would like to display the details of an airport. This will require the relevant record to be retrieved from the database table /dmo/airport.

Prerequisites

If you cannot find table /dmo/airport or any other development object starting with /DMO/, you have to import the ABAP Flight Reference Scenario for the ABAP RESTful Application Programming Model into your system. To do so, please follow the instructions under the following link:https://github.com/SAP-samples/abap-platform-refscen-flight

Steps

  1. Create class zcl_##_select. Ensure that it uses the interface, IF_OO_ADT_CLASSRUN. When you are prompted to assign the class to a transport request, use the transport request that you created in a previous task.

    1. Choose File → New → ABAP Class.

    2. Enter your package, ZS4D100_##, where ## is your group number. 

    3. Enter the name, ZCL_##_SELECT, where ## is your group number and enter a description for your class.

    4.  Choose Add... (next to the Interfaces group box).

    5. Enter the filter text, IF_OO_ADT_CLASSRUN. Double-click the matching entry in the hit list.

    6. Choose Next.

    7. Select Choose from requests in which I am involved and your own transport request.

    8. Choose Finish.

  2. Define a simple structure called ls_airport to hold the airport data from database table, /dmo/airport.

    1. See solution code

  3. Enter code that retrieves a record from the database table /dmo/airport for the airport with the filter of airport_id = ‘FRA’. Store the following fields in the corresponding fields of ls_airport.

    1. See solution code

  4. Check the return code (SY-SUBRC) to see if the data retrieval was successful. If so, output the structure ls_airport, otherwise, output the text No data found.

    1. See solution code

  5. Activate and test the class and run by selecting F9 on your keyboard.

    1. Activate and test the class. Use the source code and check the following.

      Code snippet
      * Solution Code:

CLASS zcl_s4d100_select__sol DEFINITION PUBLIC FINAL CREATE PUBLIC .

PUBLIC SECTION.
  INTERFACES if_oo_adt_classrun .
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.

CLASS zcl_s4d100_select__sol IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.
    DATA ls_airport TYPE /dmo/airport.

    SELECT SINGLE FROM /dmo/airport
       FIELDS airport_id, name, city, country
        WHERE airport_id = 'FRA'
        INTO CORRESPONDING FIELDS OF @ls_airport.

    IF sy-subrc = 0. "Data returned
      out->write( ls_airport ).
    ELSE.
      out->write( |No data found!| ).
    ENDIF.

ENDMETHOD.
ENDCLASS.
      Expand

      Practice

      ExerciseStart Exercise

Log in to track your progress & complete quizzes