Ansicht
Dokumentation

ABAPSHEET_ABAP_SQL - SHEET ABAP SQL

General Material Data SUBST_MERGE_LIST - merge external lists to one complete list with #if... logic for R3up
This documentation is copyright by SAP AG.

: Working with Persisted Data in Database Tables

This cheat sheet provides quick information about working with persisted data in database tables using . Furthermore, it serves as a collection of syntax and code snippets in one place for your reference. For more details, refer to the respective topic in the ABAP Keyword Documentation.

Database Tables in AS ABAP in a Nutshell

Database tables in AS ABAP ...

are objects of the ABAP Dictionary (DDIC)

consist of table rows and columns; each row represents a data record whose components (or fields) are available in columns; each component has a data type

are relational database tables, i. e. information can be stored in multiple database tables that are related to each other.

For example, there might be a table containing information on flight connections, flight destinations and times, another table is related to this one and includes further details on the flights like occupied seats in the plane or price details.

Such tables define a relationship using foreign key relations.

have at least one key, i.e. the primary key, to uniquely identify table rows; this might be one or more columns at the beginning of each database table.

are either cross-client or client-specific to keep the data separated; client-specific tables, which are the vast majority of database tables, include a client field (often named MANDT) as their first key field.

ensures that a statement only manipulates data from the current client

have a flat structure type.

are physically created on the database when activated - in contrast to internal tables. Plus, a globally available structured type of the same name is created, too. Hence, in an ABAP program, a database table's name can be used to declare data objects, for example, internal tables. These can be accessed by , too.

are primarily processed through statements including structures and internal tables that allow a single row or multiple rows of data at once to be stored.

Excursion: Views

Views ...

are further ABAP Dictionary objects for grouping particular data.

combine columns of one or more database tables.

usually realize a join with defined join conditions.

Similar to database tables, the columns of such a view form a flat structure. Hence, the view's name can be used to declare data objects, too.

The views can be accessed by , especially for reading purposes using SELECT.

"Classic" DDIC Views ...

are the oldest form of views.

can be accessed by for read and write operations, however, writing is supported if the view is created with only one database table

can only be created in the ABAP Workbench

"Modern" Views (since release )

External views as proxies for SAP HANA views (attribute view, analytic view, calculation view)

SAP HANA Views are entities of the SAP HANA database that are defined using the SAP HANA Studio.

They are based on HANA-specific data types.

Using external views of the ABAP dictionary, you can make those SAP HANA views "known" to the ABAP program. In doing so, the external views can be used like classic DDIC views as structured data types and as a source for reading operations with .

To be used only if the central database of the AS ABAP is a SAP HANA database.

ABAP Core Data Services (ABAP CDS) ...

serve the purpose of defining semantically rich data models.

have a lot more options than classic views, for example, they support annotations (provide information about views or individual fields), data sources can be combined using associations, unions are possible, or views can be defined with input parameters.

are used like a classic database view as structured data types and used as a source for reading operations with (using SELECT).

are created using Data Definition Language (DDL) in the ADT (that is, a source code editor, in contrast to a form-based editor)

are, in contrast to External Views, supported by all database systems (that support the ABAP CDS characteristics).

ABAP-specific form of standard Structured Query Language (SQL) which is the common language to access database tables.

What happens behind the scenes when using an statement?

Generally speaking, tables in relational database systems have a programming interface allowing table access using standard SQL, however, these interfaces are not entirely uniform and can have individual characteristics.

To make AS ABAP independent of the database used, the statements are converted to the corresponding Native SQL statements of the current database system. In doing so, allows a hassle-free and uniform access to the database tables no matter what database system is used

The main keywords to read and change data are the following:

Keyword	Purpose
SELECT	Reads data from database tables
INSERT	Adds lines to database tables
UPDATE	Changes the content of lines of database tables
MODIFY	Inserts lines into database tables or changes the content of existing lines
DELETE	Deletes lines from database tables

Read Data Using SELECT

Basic Syntax

You use SELECT statements to read records from the database, either by accessing a database table directly or via a view. The SELECT statement includes several clauses that serve different purposes. The following code snippet shows the basic syntax:

SELECT FROM source "What db tables or views to read from.
  FIELDS field_list "What columns should be read.
  WHERE condition "Specifies conditions on which rows should be read.
  INTO target. "Data object into which data should be read.

There are further clauses that are dealt with further down.

Especially in older ABAP programs, you will see other forms of the SELECT syntax that you should no longer use. For example, the INTO clause should be placed after the other clauses. This was not possible for older statements.

The list of fields can also directly follow the SELECT keyword and be positioned before the FROM clause. In this case, the FIELDS clause must not be specified. The following two code snippets are basically the same:

SELECT FROM dbtab
  FIELDS comp1, comp2, comp3
  ...

SELECT comp1, comp2, comp3
  FROM dbtab
  ...

Using SELECT 1: Basics

Read all fields of single row into structure. The read result can be either stored in an existing structure (struc) or a structure that is declared inline. Specify an asterisk (*) to indicate that all fields should be read. Alternatively, you can list all the fields separated by comma. However, select only those fields that you are really interested in as a rule for performance reasons.

The assumption is that struc has the same structure type as the database table dbtab.

As an alternative to the syntax below you can also omit the addition FIELDS as follows: SELECT SINGLE * FROM dbtab WHERE ....

ABAP variables must be preceded by @ when they interfere with database tables. See host variables.

SELECT SINGLE FROM dbtab
  FIELDS *
  WHERE ...
  INTO @struc. "Existing structure of the same type as dbtab.

SELECT SINGLE FROM dbtab
  FIELDS *
  WHERE ...
  INTO @DATA(lv_struc). "Structure declared inline.

Read all fields of multiple rows into internal table. The read result can be either stored in an existing internal table (itab) or an internal table that is declared inline. The assumption is that itab has the same structure type as the database table.

SELECT FROM dbtab
  FIELDS *
  WHERE ...
  INTO TABLE @itab.

SELECT FROM dbtab
  FIELDS *
  WHERE ...
  INTO TABLE @DATA(lv_itab).

SELECT loop. Sequentially read multiple rows into a structure. If the row that is found and returned should be kept, you might further process it within an IF sy-subrc = 0. statement.

SELECT FROM dbtab
  FIELDS *
  WHERE ...
  INTO @struc.
    IF sy-subrc = 0.
      APPEND struc TO itab. "For example, add the line to a table.
    ENDIF.
ENDSELECT.

Read selected fields of a single row into existing structure that has a different structure type. On the one hand, only particular columns are read by specifying the names in the FIELD clause. On the other hand, if you choose to return the result in a structure that has a different structure type, the CORRESPONDING FIELDS OF addition must be used so as not to mess up the returned result.

If only INTO is used, the selected columns must be in the correct order fitting to the structure type of the target variable. Only the content of columns for which there are components of the same name in the structure of the target is read from the result set.

If identically named components have different types, the system tries to convert the content of source fields into the type of the target field. In this case, there is a risk of data loss and runtime errors due to conversion errors.

SELECT SINGLE FROM dbtab
  FIELDS comp1, comp2, comp3
  WHERE ...
  INTO CORRESPONDING FIELDS OF @diff_struc.

Read selected fields of multiple rows into an existing internal table that has a different structure type.

SELECT FROM dbtab
  FIELDS comp1, comp2, comp3
  WHERE ...
  INTO CORRESPONDING FIELDS OF TABLE @diff_itab.

Using SELECT 2: Clause Variations and Additions

SELECT/FROM clause:

Check existence of a row in a database table

SELECT SINGLE @abap_true "Instead of @abap_true, you could use 'X'
  FROM dbtab
  WHERE ...
  INTO @DATA(exists).
IF exists = abap_true.
...
ENDIF.

Exclude duplicate rows from a multirow result set using DISTINCT. The duplicate entries might occur due to a non-unique WHERE clause.

SELECT DISTINCT comp1
  FROM dbtab
  WHERE ...
  INTO TABLE @itab.

Set new column names with AS

SELECT FROM dbtab
  FIELDS comp1 AS alias1, comp2 AS alias2, comp3 AS alias3
  WHERE ...
  INTO TABLE @DATA(itab_as).

Get data from a database table in another client. Note that there are several variants of the USING CLIENT addition, for example, you can also specify ALL CLIENTS to select from database tables in all clients. Furthermore, the USING CLIENT addition is also available for the statements that modify database table entries further down.

SELECT *
  FROM dbtab USING CLIENT '000'
             "ALL CLIENTS
  WHERE ...
  INTO TABLE @itab.

Get aggregate data using an SQL expression. See the ABAP keyword documentation for more information on the various SQL expressions. In the example below, the sum of all values in the specified column is calculated.

SELECT SUM( comp3 )
  FROM dbtab
  WHERE ...
  INTO @DATA(sum).

Get aggregate data using multiple SQL expressions. If multiple SQL expressions are used, aliases must be defined. In the example below, the sum of all values in the specified column is calculated. Furthermore, the average of the values of a column is calculated. Here, the data type in which the average value is returned is specified explicitly to get a more readable result.

SELECT SUM( comp2 ) AS sum
             AVG( comp3 AS DEC( 14,4 ) ) AS average
  FROM dbtab
  WHERE ...
  INTO @DATA(sql_expr).

Get aggregate data using CASE expressions

DATA(status_a) = 'A'.
  DATA(status_b) = 'B'.
  DATA(status_c) = 'C'.
SELECT comp1, comp2, comp3,
    CASE WHEN comp4 = 100 THEN @status_a
         WHEN comp4 < 50 AND comp5 > 200 THEN @status_b
         ELSE @status_c
    END AS status
  FROM dbtab
  WHERE ...
  INTO TABLE @DATA(itab_case).

FIELD clause:

Get aggregate data using SQL expressions. In the example below, the median value of all values of a specified column is calculated. Furthermore, a subtraction is carried out for the values of two columns.

SELECT FROM dbtab
  FIELDS MEDIAN( comp1 ) AS median_value, comp2 - comp4 AS sub
  WHERE ...
  INTO @DATA(median_result).

WHERE clause:

Various WHERE clause options to restrict the result. Note that without a WHERE clause, all rows are considered for the operation. The clause parts that are commented out in the following code snippet just demonstrate how the WHERE clause might look like and which expressions might be used. There are more options available. See the topic SQL Conditions in the ABAP Keyword Documentation for more information on those options.

SELECT FROM dbtab
  FIELDS comp1, comp2, comp3
  WHERE comp1 = 'abc' "Equals some value
        "comp2 > 100 "Greater than some value
        "Not equals plus an additional condition that must be considered
        "comp2 <> 100 AND comp4 = 'xyz'
        "(Not) between a value range; including OR operator
        "comp1 BETWEEN 1 AND 10 OR comp3 NOT BETWEEN 20 AND 30
        "A character literal has a certain pattern, preceded and
        "followed by any string.
        "comp1 LIKE '%XYZ%'
        "The second character is not Y. _ stands for any character.
        "comp1 NOT LIKE '_Y%'
        "Contains one of the values specified in the parentheses
        "comp1 IN ( 'ABC', 'DEF', 'GHI' )
        "Does not contain one of the values specified in the parentheses
        "comp1 NOT IN ( 'JKL', 'MNO' )
  INTO TABLE @DATA(itab_where).

Get aggregate data using expressions. In below example, a subtraction is carried out for the values of two columns.

SELECT FROM dbtab
  FIELDS comp1, comp2, comp3
  WHERE comp4 - comp5 < 10
  INTO @DATA(result).

INTO clause:

Restrict absolute number of returned table rows using the addition UP TO n ROWS. In below example, only five rows should be returned at most.

SELECT * FROM dbtab
WHERE ...
INTO TABLE @DATA(itab_upto) UP TO 5 ROWS.

Append the result set to an existing internal table that has the same structure type as the database table. By appending, you avoid the deletion of existing rows in internal tables.

SELECT * FROM dbtab
WHERE ...
APPENDING TABLE @itab.

Append the result set to an existing internal table that has a different structure type than the database table.

SELECT * FROM dbtab
WHERE ...
APPENDING CORRESPONDING FIELDS OF TABLE @itab.

Read single fields into variables. You can also process multiple table entries by using a SELECT loop. Note that the number of columns specified (here in FIELDS clause) must match the number of elements in the INTO clause.

SELECT FROM dbtab
  FIELDS comp1, comp2, comp3
  WHERE ...
  INTO (@res1,@res2,@res3).

Read into packages when reading into internal tables. The package size defines how many rows should be selected in one execution of a loop. This is handy in case a very large amount of data has to be processed that might be too large for the memory capacity of an internal table and so avoids program termination. The package size is specified by an integer. See more information here: PACKAGE SIZE n.

SELECT FROM dbtab
  FIELDS comp1, comp2, comp3
  WHERE ...
  INTO TABLE @DATA(itab_pack) PACKAGE SIZE i.
...
ENDSELECT.

Using SELECT 3: Further Clauses

GROUP BY clause: Combine groups of rows of the result set into one row.

SELECT FROM dbtab
  FIELDS comp1, MIN( comp2 ) AS min, MAX( comp3 ) AS max
  WHERE ...
  GROUP BY comp1
  INTO ...

HAVING clause: Combine groups of rows of the result set into one row and set conditions for aggregated rows.

SELECT FROM dbtab
  FIELDS comp1, comp2, comp3
  WHERE ...
  GROUP BY comp1, comp2, comp3
  HAVING comp1 LIKE '%XYZ%' AND SUM( comp7 ) > 100
  INTO ...

ORDER BY clause: Sort result set.

SELECT FROM dbtab
  FIELDS *
  WHERE ...
  ORDER BY PRIMARY KEY
           "comp2 ASCENDING
           "comp2 DESCENDING
  INTO ...

Using SELECT 4: Read from Multiple Tables

Using FOR ALL ENTRIES: Read data from a table depending on the content of an internal table.

Ensure that the internal table from which to read is not initial. It is recommended that a subquery is used, which is shown in the next example, and a SELECT statement that reads from the internal table (... ( SELECT ... FROM itab2 WHERE ...).

IF ( 0 < lines( itab2 ) ).
SELECT comp1, comp2, comp3
  FROM dbtab
  FOR ALL ENTRIES IN @itab2
  WHERE comp1 = @itab2-comp1 ...
  INTO TABLE @itab1
ENDIF.

Using a subquery: Read from a database table depending on data of another database table. More information: EXISTS. The components of the table are referenced by ~.

SELECT comp1, comp2, comp3
  FROM dbtab AS tab1
  WHERE EXISTS
   ( SELECT comp1 FROM dbtab2
     WHERE comp1 = tab1~comp1 AND comp2 = tab1~comp2 )
  INTO ...

Using an inner join: Combine data of multiple database tables. In this kind of join, columns with rows of the left-hand side and those of the right-hand side are only joined if the rows meet the join conditions (ON ...). If there are no equivalent entries in the first or second table, the rows are not joined.

SELECT a~comp1, a~comp2, b~comp3, c~comp4
  FROM dbtab1 AS a
  INNER JOIN dbtab2 AS b
   ON a~comp1 = b~comp1 AND a~comp2 = b~comp2
  INNER JOIN dbtab3 AS c
   ON a~comp1 = c~comp1
  WHERE ...
  INTO ...

Using a left outer join: Combine data of multiple database tables. The columns of each row on the right-hand side that do not meet the ON condition are filled with initial values and linked with the columns of the left-hand side. If the conditions of the WHERE clause have been met, each row on the left-hand side of the left outer join produces at least one row in the selection, irrespective of the ON condition.

SELECT a~comp1, a~comp2, b~comp3,
  FROM dbtab1 AS a
  LEFT OUTER JOIN dbtab2 AS b
   ON a~comp1 = b~comp1
  WHERE ...
  INTO ...

Using a UNION: Combine data of multiple database tables. The columns of the result set keep the names defined in the statement on the left of UNION. The result set of rows of the SELECT statement on the right of UNION are inserted into the results set of the SELECT statement on the left of UNION.

SELECT FROM dbtab1
  FIELDS ...
  WHERE ...
UNION
  SELECT FROM dbtab2
   FIELDS ...
   WHERE ...
  INTO ...

There are more join variants available. See the ABAP Keyword Documentation on joins for more information.

Using SELECT 5: Excursions

Use SELECT to read from DDIC views or CDS views or use an internal table as data source to be read from.

See the executable example.

Changing Data in Database Tables

Using INSERT

"Insert individual row into database table
INSERT dbtab FROM @row.
INSERT INTO dbtab VALUES row. "Alternative syntax.

"Insert multiple rows (from an internal table) into database table.
INSERT dbtab FROM TABLE @itab.

"Insert multiple rows accepting duplicate keys. All rows that would
"produce duplicate entries in the database table regarding the keys
"are discarded and sy-subrc is set to 4.
INSERT dbtab FROM TABLE @itab ACCEPTING DUPLICATE KEYS.

Using UPDATE

"Change content by overwriting entire rows from a work area
UPDATE dbtab FROM @row.

"Change content by overwriting entire rows from an internal table
UPDATE dbtab FROM TABLE @itab.

"Change content of specific columns without overwriting existing
"content of rows
UPDATE dbtab SET comp4 = 'XYZ' WHERE ... .

Using MODIFY

"Insert rows into database table or change existing rows
MODIFY dbtab FROM itab.

Using DELETE

"Delete table row from work area.
DELETE dbtab FROM row.

"Delete table rows by condition
DELETE FROM dbtab WHERE ... .

"Delete complete table
DELETE FROM dbtab.

Demonstration Program

The example - Working with Persisted Data in Database Tables demonstrates all of the syntactical options mentioned above in one program.

rdisp/max_wprun_time - Maximum work process run time ROGBILLS - Synchronize billing plans
This documentation is copyright by SAP AG.

Length: 30232 Date: 20240509 Time: 123839 sap01-206 ( 295 ms )

Ansicht Dokumentation