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