SAP ABAP - Open SQL Overview

Open SQL indicates the subset of ABAP statements that enable direct access to the data in the central database of the current AS ABAP. Open SQL statements map the Data Manipulation Language functionality of SQL in ABAP that is supported by all database systems.

The statements of Open SQL are converted to database specific SQL in the Open SQL interface of the database interface. They are then transferred to the database system and executed. Open SQL statements can be used to access database tables that are declared in the ABAP Dictionary. The central database of AS ABAP is accessed by default and also access to other databases is possible via secondary database connections.

Whenever any of these statements are used in an ABAP program, it is important to check whether the action executed has been successful. If one tries to insert a record into a database table and it is not inserted correctly, it is very essential to know so that the appropriate action can be taken in the program. This can done using a system field that has already been used, that is SY-SUBRC. When a statement is executed successfully, the SY-SUBRC field will contain a value of 0, so this can be checked for and one can continue with the program if it appears.

The DATA statement is used to declare a work area. Let's give this the name 'wa_customers1'. Rather than declaring one data type for this, several fields that make up the table can be declared. The easiest way to do this is using the LIKE statement.

INSERT Statement

The wa_customers1 work area is declared here LIKE the ZCUSTOMERS1 table, taking on the same structure without becoming a table itself. This work area can only store one record. Once it has been declared, the INSERT statement can be used to insert the work area and the record it holds into the table. The code here will read as 'INSERT ZCUSTOMERS1 FROM wa_customers1'.

The work area has to be filled with some data. Use the field names from the ZCUSTOMERS1 table. This can be done by forward navigation, double clicking the table name in the code or by opening a new session and using the transaction SE11. The fields of the table can then be copied and pasted into the ABAP editor.

Following is the code snippet −

DATA wa_customers1 LIKE ZCUSTOMERS1. 
wa_customers1-customer = '100006'. 
wa_customers1-name = 'DAVE'. 
wa_customers1-title = 'MR'. 
wa_customers1-dob = '19931017'. 
INSERT ZCUSTOMERS1 FROM wa_customers1.

CHECK statement can then be used as follows. It means that if the record is inserted correctly, the system will state this. If not, then the SY-SUBRC code which will not equal zero will be displayed. Following is the code snippet −

IF SY-SUBRC = 0. 
   WRITE 'Record Inserted Successfully'.  
ELSE. 
   WRITE: 'The return code is ', SY-SUBRC. 
ENDIF.

Check the program, save, activate the code, and then test it. The output window should display as 'Record Inserted Successfully'.

CLEAR Statement

CLEAR statement allows a field or variable to be cleared out for the insertion of new data in its place, allowing it to be reused. CLEAR statement is generally used in programs and it allows existing fields to be used many times.

In the previous code snippet, the work area structure has been filled with data to create a new record to be inserted into the ZCUSTOMERS1 table and then a validation check is performed. If we want to insert a new record, CLEAR statement must be used so that it can then be filled again with the new data.

UPDATE Statement

If you want to update one or more existing records in a table at the same time then use UPDATE statement. Similar to INSERT statement, a work area is declared, filled with the new data that is then put into the record as the program is executed. The record previously created with the INSERT statement will be updated here. Just edit the text stored in the NAME and TITLE fields. Then on a new line, the same structure as for the INSERT statement is used, and this time by using the UPDATE statement as shown in the following code snippet −

DATA wa_customers1 LIKE ZCUSTOMERS1. 
wa_customers1-customer = '100006'. 
wa_customers1-name = 'RICHARD'. 
wa_customers1-title = 'MR'. 
wa_customers1-dob = '19931017'. 
UPDATE ZCUSTOMERS1 FROM wa_customers1.

As UPDATE statement gets executed, you can view the Data Browser in the ABAP Dictionary to see that the record has been updated successfully.

MODIFY Statement

MODIFY statement can be considered as a combination of the INSERT and UPDATE statements. It can be used to either insert a new record or modify an existing record. It follows a similar syntax to the previous two statements in modifying the record from the data entered into a work area.

When this statement is executed, the key fields involved will be checked against those in the table. If a record with these key field values already exist, it will be updated. If not, then a new record will be created.

Following is the code snippet for creating a new record −

CLEAR wa_customers1.
 
DATA wa_customers1 LIKE ZCUSTOMERS1. 
wa_customers1-customer = '100007'. 
wa_customers1-name = 'RALPH'. 
wa_customers1-title = 'MR'. 
wa_customers1-dob = '19910921'. 
MODIFY ZCUSTOMERS1 FROM wa_customers1.

In this example, CLEAR statement is used so that a new entry can be put into the work area, and then customer (number) 100007 is added. Since this is a new, unique key field value, a new record will be inserted, and another validation check is executed.

When this is executed and the data is viewed in the Data Browser, a new record will have been created for the customer number 100007 (RALPH).

The above code produces the following output (table contents) −