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 |
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.
ENDSELECT.
WRITE :/ 1gwa_employee-id,9 gwa_employee-name,
17 gwa_employee-place,27 gwa_employee-phone, 39 gwa_employee-dept_id.
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.
Selective Reading
In Reading Data using Open SQL we have read all the rows from the database. 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.
Example:
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.
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.
Output:
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 ‘*’.
Example:
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.
Output:
To select a single record from the database we 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.
Example:
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.
Output:
