Implementing Basic SELECT Statements

Objectives

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 Manipulation Language (DML)
DML comprises of the statements that you need to work with information in database tables. These are INSERT, for adding new records, SELECT, for reading data, UPDATE, to change existing records, and DELETE, to delete table contents. These statements are reflected in ABAP in the form of ABAP SQL.
Data Definition Language (DDL)
Data Definition Language provides you with commands that enable you to create and delete database tables and other database artifacts such as schemata, table indexes, and views. In an SAP system, you perform these tasks using dedicated editors of ADT, not with ABAP programs. One example is the editor for database table definitions we saw earlier.
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.

Note

The name change from Open SQL and ABAP SQL also illustrates that as off release 7.53 ABAP only supports SAP HANA as DBMS.

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 ABAP SQL, the SQL variant that is integrated into ABAP. 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 newer ABAP releases 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
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.
Tasks of the Database Interface
The database interface does not just translate statements; it is also responsible for ABAP specific tasks like, for example, automatic client-handling.

Single Record Reading from the Database

The SELECT Statement in ABAP

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.

Note

You will see other forms of SQL syntax in ABAP. These are older and have been retained to ensure compatibility. You should get used to using the modern syntax, as it provides far more functions and features than the old form.

The figure, Example 1: Reading Single Field of Single Record, illustrates a SELECT statement that reads a single value from the database. The FROM clause tells us that the statement reads from database table /DMO/CONNECTION. The option, SINGLE, after the keyword, SELECT, indicates that only one row (a single record) is read. This row is identified in the WHERE clause by providing key filter values for key fields carrier_id and connection_id. Keep in mind, that the database interface will add a filter on the remaining key field client.

The FIELDS clause lists only one column of the table: column AIRPORT_FROM_ID.

The INTO clause has to match the rest of the statement. In our example, this specifies the variable airport_from_id as the target object, a scalar data object of identical type as table field, airport_from_id.

Note

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.

The figure, Example 2: Reading Several Fields of Single Record, illustrates a SELECT statement that reads two values from the same record of the database.

This time, the FIELDS clause lists two columns of the table; column AIRPORT_FROM_ID and column AIRPORT_TO_ID.

To match this, the INTO clause specifies the variables airport_from_id and airport_to_id as the target objects. They are separated by a comma and surrounded by pair of brackets to make it clear that together they form the target of the SELECT statement.

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
    Copy code
    Switch to dark mode
    123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657
    
    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
**********************************************************************
    SELECT SINGLE
      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
**********************************************************************
    SELECT SINGLE
      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
**********************************************************************
    SELECT SINGLE
      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 }.| ).

    ELSE.

      out->write( `----------`  ).
      out->write( `Example 3:`  ).
      out->write( |There is no flight XX 1234, but still airport_from_id = {  airport_from_id }!| ).

    ENDIF.
  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.

Read Data from a Database Table

In this exercise, you extend your local class with attributes for the departure airport and the destination airport, and read the values for these attributes from a database table.

Template:

  • /LRN/CL_S4D400_CLS_CONSTRUCTOR (global Class)

Solution:

  • /LRN/CL_S4D400_DBS_SELECT (global Class)

Task 1: Copy Template

Copy the template class. Alternatively, copy your solution of the previous exercise.

Steps

  1. Copy the class /LRN/CL_S4D400_CLS_CONSTRUCTOR to a class in your own package (suggested name: ZCL_##_SELECT, where ## stands for your group number).

    1. Open the source code of the global class /LRN/CL_S4D400_CLS_CONSTRUCTOR.

    2. Link the Project Explorer view with the editor.

    3. In the Project Explorer view, right-click class /LRN/CL_S4D400_CLS_CONSTRUCTOR to open the context menu.

    4. From the context menu, choose Duplicate ....

    5. Enter the name of your package in the Package field. In the Name field, enter the name ZCL_##_SELECT, where ## stands for your group number.

    6. Adjust the description and choose Next.

    7. Confirm the transport request and choose Finish.

Task 2: Declare Additional Attributes

Extend the local class lcl_connection with the private instance attributes airport_from_idand airport_to_id. Add some output for the new attributes to the implementation of the method get_output.

Note

The new attributes are not filled yet. In order to fill them we will add a SELECT statement to the constructor in one of the next tasks of this exercise.

Steps

  1. Switch to the local class lcl_connection.

    1. In the global class, choose Local Types.

  2. Add the following private attributes to the class definition:

    Attributes

    Attribute NameScopeData Type
    airport_from_idinstance/DMO/AIRPORT_FROM_ID
    airport_to_idinstance/DMO/AIRPORT_TO_ID

    1. Add the highlighted code:

      Code Snippet
      Copy code
      Switch to dark mode
      1234567
      
  PRIVATE SECTION.
    DATA carrier_id    TYPE /dmo/carrier_id.
    DATA connection_id TYPE /dmo/connection_id.

ENDCLASS.

  3. Extend the implementation of the method get_output. Append more string templates to the returning parameter r_output. Embed the new attributes as expressions into the string templates.

    1. Navigate to the implementation of the method get_output.

    2. Add the highlighted code:

      Code Snippet
      Copy code
      Switch to dark mode
      1234
      
    APPEND |--------------------------------|             TO r_output.
    APPEND |Carrier:     { carrier_id      }|             TO r_output.
    APPEND |Connection:  { connection_id   }|             TO r_output.

  4. Activate the class. Execute it and analyze the console output.

    1. Press Ctrl + F3 to activate the class.

    2. Press F9 to run the class.

Task 3: Analyze the Database Table

Analyze the definition of the database table /DMO/CONNECTION.

Steps

  1. Open the development object that contains the definition of the database table /DMO/CONNECTION.

    1. From the eclipse toolbar, choose Open ABAP Development Object or press Ctrl + Shift + A.

    2. In the input field, enter /dmo/con as a search string.

    3. In the list of matching items, click on /DMO/CONNECTION (Database Table) and choose OK.

  2. Open the Tooltip Description for the database table /DMO/CONNECTION.

    1. Click on /dmo/connection after the keyword define table and press F2 to show the tooltip description.

Task 4: Read Data from the Database

In the method constructor of the local class lcl_connection, implement a SELECT statement that reads values for the new attributes from the database table /DMO/CONNECTION.

Steps

  1. Return to the local class lcl_connection in your global class.

    1. In the editor view of Eclipse, open tab ZCL_##_SELECT.

    2. In the global class, choose Local Types.

  2. Navigate to the implementation of the method constructor.

    1. Search for the code line METHOD constructor..

  3. After the ENDIF. statement, add a SELECT statement that reads a single record from database table /DMO/CONNECTION.

    1. Add the highlighted code:

      Code Snippet
      Copy code
      Switch to dark mode
      12345
      
  IF i_carrier_id IS INITIAL OR i_connection_id IS INITIAL.
    RAISE EXCEPTION TYPE cx_abap_invalid_value.
  ENDIF.

  4. Implement the FIELDS clause. Read the table fields airport_from_id and airport_to_id.

    Hint

    Use auto-completion (Ctrl + Space) to enter the field names.

    1. After FROM /DMO/CONNECTION enter FIELDS.

    2. After a blank, press Ctrl + Space and choose airport_from_id.

    3. After a comma and a blank press Ctrl + Space again and choose airport_to_id.

    4. The complete FIELDS clause should look like this:

      Code Snippet
      Copy code
      Switch to dark mode
      123
      
      FIELDS DepartureAirport, DestinationAirport

  5. Implement the WHERE condition. Restrict all key fields of the database table (except for the client field) with the values of importing parameters i_carrier_id and i_connection_id. Do not forget to escape the parameters with prefix @.

    Hint

    Use auto-completion (Ctrl + Space) to enter the element names and parameter names.

    1. Add the following code after the FIELDS clause:

      Code Snippet
      Copy code
      Switch to dark mode
      1234
      
     WHERE carrier_id    = @i_carrier_id
       AND connection_id = @i_connection_id

  6. Implement the INTO clause. Store the SELECT result in the attributes airport_from_id, and airport_to_id. Do not forget to escape the attributes with prefix @.

    Hint

    Use auto-completion (Ctrl + Space) to enter the attribute names.

    1. Add the highlighted code:

      Code Snippet
      Copy code
      Switch to dark mode
      1234
      
 
    WHERE carrier_id    = @i_carrier_id
      AND connection_id = @i_connection_id

    2. The complete SELECT statement should look like this:

      Code Snippet
      Copy code
      Switch to dark mode
      12345678
      
SELECT SINGLE
      FROM /dmo/connection
    FIELDS airport_from_id, airport_to_id
     WHERE carrier_id    = @i_carrier_id
       AND connection_id = @i_connection_id
      INTO ( @airport_from_id, @airport_to_id ).

  7. Implement error handling after the SELECT statement. Check the content of the system field sy-subrc. If it does not equal zero, raise exception CX_ABAP_INVALID_VALUE.

    1. Add the following code after the SELECT statement:

      Code Snippet
      Copy code
      Switch to dark mode
      12345
      
   IF sy-subrc <> 0.
      RAISE EXCEPTION TYPE cx_abap_invalid_value.
   ENDIF.

  8. Activate the class. Execute it and analyze the console output. Check that the output for the new attributes displays data.

    1. Press Ctrl + F3 to activate the class.

    2. Press F9 to run the class.

Log in to track your progress & complete quizzes