Get Started with ABAP Programming on SAP BTP

Implementing Basic SELECT Statements

After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Describe basic features of ABAP SQL
  • Read single values from the database

Basic ABAP SQL Syntax

Structured Query Language (SQL)

All relational database systems use a variant of Structured Query Language (SQL) to allow you to work with them. Standard SQL consists of three main components:

Data Control Language (DCL)
Data Control Language is used in SQL to restrict access to data in the database for a particular user. It is not used in its classic form in ABAP, since the users at database level do not correspond one-to-one with the end users. Consequently, ABAP has its own authorization concept.

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 DDBMS, 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

The SELECT Statement in ABAP

To read data from the database, you use the SELECT statement.

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.

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.

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 syntax of the SELECT statement is explained here. You will also see some examples of the SELECT statement.

The at sign (@) identifies airport_from_id as the name of an ABAP data object. It is mandatory for all variables and constants that you use in an ABAP SQL statement. It is needed to avoid ambiguities if, for example, a data object and table field have the same name.

When you implement a SELECT statement you always have to take into account that there could be no result, either because the database table does not contain any data at all or because it does not contain any rows that fulfill the conditions in the WHERE clause. In the example from the figure above, the database table does not contain a row with carrier_id = 'XX' and connection_id = '1234'.

ABAP SQL use system field SY-SUBRC to indicate a successful or unsuccessful execution of a statement. System field SY-SUBRC is of type integer. Initial value 0 always indicates a successful execution. If, after a SELECT statement, SY-SUBRC contains the value 4, this indicates that the database returned an empty result.

If the database returns an empty result, ABAP SQL does NOT touch the target variable after INTO!

In particular, the target variable is not initialized in case of an error.

It is recommended that you evaluate the content of system field sy-subrc immediately after each SELECT statement.

Try It Out: SELECT Examples

  1. Like in the first exercise of this course, create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code snippet
        DATA airport_from_id TYPE /DMO/airport_from_id.
        DATA airport_to_id   TYPE /DMO/airport_to_id.
        DATA airports TYPE TABLE OF /DMO/airport_from_id.
    * Example 1: Single field from Single Record
          FROM /dmo/connection
          FIELDS airport_from_id
          WHERE carrier_id    = 'LH'
            AND connection_id = '0400'
            INTO @airport_from_id.
        out->write( `----------`  ).
        out->write( `Example 1:`  ).
        out->write( |Flight LH 400 departs from {  airport_from_id }.| ).
    * Example 2: Multiple Fields from Single Record
          FROM /dmo/connection
          FIELDS airport_from_id, airport_to_id
          WHERE carrier_id    = 'LH'
            AND connection_id = '0400'
            INTO (  @airport_from_id, @airport_to_id ).
        out->write( `----------`  ).
        out->write( `Example 2:`  ).
        out->write( |Flight LH 400 flies from {  airport_from_id } to { airport_to_id  }| ).
    * Example 3: Empty Result and sy-subrc
          FROM /dmo/connection
          FIELDS airport_from_id
          WHERE carrier_id    = 'XX'
            AND connection_id = '1234'
            INTO @airport_from_id.
        IF sy-subrc = 0.
          out->write( `----------`  ).
          out->write( `Example 3:`  ).
          out->write( |Flight XX 1234 departs from {  airport_from_id }.| ).
          out->write( `----------`  ).
          out->write( `Example 3:`  ).
          out->write( |There is no flight XX 1234, but still airport_from_id = {  airport_from_id }!| ).
    Copy code
  3. Press CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output. Debug the program, play around with the source code to get familiar with the concepts.

Save progress to your learning plan by logging in or creating an account