OPEN SQL

What is Open SQL?

Open SQL is a set of ABAP statements that performs operations like reads, modifies or deletes data in the SAP database. Open SQL is independent of the database system, so the syntax of the open SQL is uniform for all the databases supported by SAP.
All open SQL statements are passed to the database interface. The DB interface converts the open SQL to native SQL and passes it on to the database.
List of Open SQL statements

Open SQL	Description
SELECT	Reads data from database
INSERT	Inserts lines to database
UPDATE	Changes the contents of lines in database
MODIFY	Inserts lines into database or changes the contents of existing lines
DELETE	Deletes lines from database

All Open SQL statements fill the following two system fields:

• SY-SUBRC – After every Open SQL statement, the system field SY-SUBRC contains the value 0 if the operation was successful, a value other than 0 if not.
• SY-DBCNT – After an open SQL statement, the system field SY-DBCNT contains the number of database lines processed.

Reading Data using Open SQL

SELECT is the open SQL statement to read the data from the database. The general syntax for SELECT statement is as follows.
SELECT      <result>
   INTO      <target>
  FROM      <source> 
[WHERE    <condition>]

Clause	Description
SELECT <result>	Specifies which columns you want to read, whether one line or many lines needs to selected, and whether duplicate entries are allowed
INTO <target>	Determines the target area into which the selected data is to be placed
FROM <source>	Specifies the database table from which the data is to be selected
WHERE <condition>	specifies which lines are to be read by specifying conditions for the selection

DATA: gwa_employee TYPE zemployee.

WRITE:/1 'Emp ID' color 5,9 'Name' color 5,17 'Place' color 5,
      27 'Phone' color 5,39 'Dept' color 5.

SELECT * FROM zemployee INTO gwa_employee.
  WRITE:/1 gwa_employee-id,9 gwa_employee-name,
        17 gwa_employee-place,27 gwa_employee-phone,
        39 gwa_employee-dept_id.
ENDSELECT.

In the above code,

• GWA_EMPLOYEE is the work area to hold one record of table ZEMPLOYEE at a time.
• SELECT * specifies all the rows and columns are read from the database.
• SELECT – ENDSELECT works in a loop, so the code between SELECT and ENDSELECT will be executed for each record found in the database table.
• WRITE statements are used to output the values in the list.
• If the SELECT statement returns any record then the value of the system variable SY-SUBRC is set to zero else a non zero value will be set.
• After the SELECT statement is executed, the value of the system variable SY-DBCNT contains the number of records read from the database. The value of SY-DBCNT is zero if no records are read from the database.

Table ZEMPLOYEE Entries

Report Output


Selective Reading using Open SQL

In Reading Data using Open SQL we have read all the rows from the database. What if we want to read only certain records that match a certain criteria? Then we need to use the where clause of the SELECT statement.
Let us write a program to read only the employees with department ID 2.

DATA: gwa_employee TYPE zemployee.

WRITE:/1 'Emp ID' COLOR 5,9 'Name' COLOR 5,17 'Place' COLOR 5,
      27 'Phone' COLOR 5,39 'Dept' COLOR 5.

SELECT * FROM zemployee INTO gwa_employee
                        WHERE dept_id = 2.
  WRITE:/1 gwa_employee-id,9 gwa_employee-name,
        17 gwa_employee-place,27 gwa_employee-phone,
        39 gwa_employee-dept_id.
ENDSELECT.

Report Output

What if we want to select only certain columns from the database table instead of all the columns? Then we need to specify the field list(field names) in the SELECT statement instead of specifying ‘*’.

SELECT id phone dept_id FROM zemployee INTO CORRESPONDING FIELDS OF
                        gwa_employee
                        WHERE dept_id = 2.
  WRITE:/1 gwa_employee-id,9 gwa_employee-name,
        17 gwa_employee-place,27 gwa_employee-phone,
        39 gwa_employee-dept_id.
ENDSELECT.

Report Output

Only columns ID, PHONE and DEPT_ID were read from the database.
To select a single record from the database use SELECT SINGLE instead of SELECT statement. SELECT SINGLE picks the first record found in the database that satisfies the condition in WHERE clause. SELECT SINGLE does not work in loop, so no ENDSELECT is required.

SELECT SINGLE * FROM zemployee INTO gwa_employee
                        WHERE dept_id = 2.
WRITE:/1 gwa_employee-id,9 gwa_employee-name,
      17 gwa_employee-place,27 gwa_employee-phone,
      39 gwa_employee-dept_id.

Report Output


Inserting Values using SAP Open SQL

INSERT is the open SQL statement to add values to the database table. First declare a work area as the line structure of database table and populate the work area with the desired values. Then add the values in the work area to the database table using INSERT statement.
The syntax for the INSERT statement is as follows.
INSERT <database table> FROM <work area>
or
INSERT INTO <database table> VALUES <work area>
If the database table does not already contain a line with the same primary key as specified in the work area, the operation is completed successfully and SY-SUBRC is set to 0. Otherwise, the line is not inserted, and SY-SUBRC is set to 4.

DATA: gwa_employee TYPE zemployee.

gwa_employee-id      = 6.
gwa_employee-name    = 'MARY'.
gwa_employee-place   = 'FRANKFURT'.
gwa_employee-phone   = '7897897890'.
gwa_employee-dept_id = 5.

INSERT zemployee FROM gwa_employee.

EMPLOYEE table entries before INSERT

EMPLOYEE table entries after INSERT


Changing Values using SAP Open SQL

UPDATE is the open SQL statement to change the values in the database table. First declare a work area as the line structure of database table and populate the work area with the desired values for a specific key in the database table. Then update the values for the specified key in the database table using UPDATE statement.
The syntax for the UPDATE statement is as follows.
UPDATE <database table> FROM <work area>
If the database table contains a line with the same primary key as specified in the work area, the operation is completed successfully and SY-SUBRC is set to 0. Otherwise, the line is not inserted, and SY-SUBRC is set to 4.

DATA: gwa_employee TYPE zemployee.

gwa_employee-id      = 6.
gwa_employee-name    = 'JOSEPH'.
gwa_employee-place   = 'FRANKFURT'.
gwa_employee-phone   = '7897897890'.
gwa_employee-dept_id = 5.

UPDATE zemployee FROM gwa_employee.

EMPLOYEE table entries before UPDATE

EMPLOYEE table entries after UPDATE

We can also change certain columns in the database table using the following syntax
UPDATE <target> SET <set1> <set 2> … [WHERE <condition>].
The WHERE clause determines the lines that are changed. If we do not specify a WHERE clause, all lines will be changed.

UPDATE zemployee SET place = 'MUMBAI' WHERE dept_id = 2.

EMPLOYEE table entries after UPDATE


Deleting Entries using SAP Open SQL

DELETE is the open SQL statement to delete entries from database table. First declare a work area as the line structure of database table and populate the work area with the  specific key that we want to delete from the database table. Then delete the entries from the database table using DELETE statement.
The syntax for the DELETE statement is as follows.
DELETE <database table> FROM <work area>
If the database table contains a line with the same primary key as specified in the work area, the operation is completed successfully and SY-SUBRC is set to 0. Otherwise, the line is not deleted, and SY-SUBRC is set to 4.

DATA: gwa_employee TYPE zemployee.

gwa_employee-id      = 6.
gwa_employee-name    = 'JOSEPH'.
gwa_employee-place   = 'FRANKFURT'.
gwa_employee-phone   = '7897897890'.
gwa_employee-dept_id = 5.

DELETE zemployee FROM gwa_employee.

EMPLOYEE table entries before DELETE

EMPLOYEE table entries after DELETE

We can also multiple lines from the table using the WHERE clause in the DELETE statement.
DELETE FROM <database table> WHERE <condition>

DELETE FROM zemployee WHERE dept_id = 2.

EMPLOYEE table entries after DELETE


Inserting or Changing Values using SAP Open SQL

MODIFY is the open SQL statement to insert or change entries in the database table. If the database table contains no line with the same primary key as the line to be inserted, MODIFY works like INSERT, that is, the line is added. If the database already contains a line with the same primary key as the line to be inserted, MODIFY works like UPDATE, that is, the line is changed.
The syntax for the MODIFY statement is as follows.
MODIFY <database table> FROM <work area>
If the database table does not already contain a line with the same primary key as specified in the work area, a new line is inserted. If the database table does already contain a line with the same primary key as specified in the work area, the existing line is overwritten. SY-SUBRC is always set to 0.

DATA: gwa_employee TYPE zemployee.

gwa_employee-id      = 6.
gwa_employee-name    = 'JOSEPH'.
gwa_employee-place   = 'FRANKFURT'.
gwa_employee-phone   = '7897897890'.
gwa_employee-dept_id = 5.

MODIFY zemployee FROM gwa_employee.

ZEMPLOYEE table entries before MODIFY

ZEMPLOYEE table entries after MODIFY

Since there was no entry with the key 6, a new entry was added to the table.

DATA: gwa_employee TYPE zemployee.

gwa_employee-id      = 6.
gwa_employee-name    = 'JOHNNY'.
gwa_employee-place   = 'LONDON'.
gwa_employee-phone   = '7897897890'.
gwa_employee-dept_id = 3.

MODIFY zemployee FROM gwa_employee.

Since there was an entry with the key 6, the values in the existing record were modified.