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:
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.
NoteThe 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.
Reading a Single Record from the Database
The SELECT Statement in ABAP
To read data from the database, you use the SELECT statement.
The syntax of the SELECT statement is explained here. You will also see some examples of the SELECT statement.
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.
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
- Like in the first exercise of this course, create a new global class that implements interface IF_OO_ADT_CLASSRUN.
- Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
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.
- Press
CTRL + F3 to activate the class and F9 to execute it as a console app. - 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
