SELECT is a keyword used in SAP ABAP programming.This tutorial covers its introduction & syntax details.
SELECT
Basic form
SELECT result [target] FROM source [where] [GROUP BY fields] [ORDER BY order].
Effect
Retrieves
an extract and/or a set of data from a database table or view (see
Relational database ). SELECT belongs to the OPEN SQL command set.
Each SELECT command consists of a series of clauses specifying different tasks:
The SELECT result clause specifies
whether the result of the selection is a table or a single record,
which columns the result is meant to have and
whether the result is allowed to include identical lines.
The
INTO target clause specifies the target area into which the selected
data is to be read. If the target area is an internal table, the INTO
clause specifies
whether the selected data is to overwrite the contents of the internal table or
whether the selected data is to be appended to the contents and
whether the selected data is to be placed in the internal table all at once or in several packets.
The INTO clause can also follow the FROM clause.
You
can omit the INTO clause. The system then makes the data available in
the table work area (see TABLES ) dbtab . If the SELECT clause includes
a “*”, the command is processed like the identical SELECT * INTO dbtab
FROM dbtab statement. If the SELECT clause contains a list a1 … an ,
the command is executed like SELECT a1 … an INTO CORRESPONDING FIELDS
OF dbtab FROM dbtab .
If the result of the selection is meant to be
a table, the data is usually (for further information, see INTO
-Klausel ) read line by line within a processing loop introduced by
SELECT and concluded by ENDSELECT . For each line read, the processing
passes through the loop once. If the result of the selection is meant
to be a single record, the closing ENDSELECT is omitted.
The FROM source clause the source (database table or view ) from which the data is to be selected. It also determines
the type of client handling,
the behavior for buffered tables and
the maximum number of lines to be read.
The
WHERE where clause specifies the conditions which the result of the
selection must satisfy. It thus determines the lines of the result
table. Normally – i.e. unless a client field is specified in the WHERE
clause – only data of the current client is selected. If you want to
select across other clients, the FROM clause must include the addition
… CLIENT SPECIFIED .
The GROUP-BY fields clause combines groups of
lines together into single lines. A group is a set of lines which
contain the same value for every database field in the GROUP BY clause.
The ORDER-BY order clause stipulates how the lines of the result table are to be ordered.
Each
time the SELECT statement is executed, the system field SY-DBCNT
contains the number of lines read so far. After ENDSELECT , SY-DBCNT
contains the total number of lines read.
The return code value is set as follows:
SY-SUBRC = 0 At least one line was read.
SY_SUBRC = 4 No lines were read.
SY-SUBRC = 8 The search key was not fully qualified.
(nur bei SELECT SINGLE ). The returned single record is any line of the solution set.
Example
Output the passenger list for the Lufthansa flight 0400 on 28.02.1995:
TABLES SBOOK.
SELECT * FROM SBOOK
WHERE
CARRID = ‘LH ‘ AND
CONNID = ‘0400’ AND
FLDATE = ‘19950228’
ORDER BY PRIMARY KEY.
WRITE: / SBOOK-BOOKID, SBOOK-CUSTOMID, SBOOK-CUSTTYPE,
SBOOK-SMOKER, SBOOK-LUGGWEIGHT, SBOOK-WUNIT,
SBOOK-INVOICE.
ENDSELECT.
Note
Performance
In
client/server environments, storing database tables in local buffers
(see SAP buffering ) can save considerable amounts of time because the
time required to make an access via the network is much more than that
needed to access a locally buffered table.
Notes
A SELECT
command on a table for which SAP buffering is defined in the ABAP/4
Dictionary is normally satisfied from the SAP buffer by bypassing the
database. This does not apply with
– SELECT SINGLE FOR UPDATE
– SELECT DISTINCT in the SELECT clause ,
– BYPASSING BUFFER in the FROM clause ,
– ORDER BY f1 … fn in the ORDER-BY clause ,
– aggregate functions in the SELECT clause ,
– when using IS [NOT] NULL WHERE condition ,
or if the generic key part is not qualified in the WHERE-Bedingung for a generically buffered table.
Authorization checks are not supported by the SELECT statement, so you must program these yourself.
In
dialog systems, the database system locking mechanism cannot always
guarantee to synchronize the simultaneous access of several users to
the same dataset. In many cases, it is therefore advisable to use the
SAP locking mechanism .
Changes to data in a database are only
finalized after a database commit (see LUW ). Prior to this, any
database update can be reversed by a database rollback (see Programming
transactions ). At the lowest isolation level (see the section on the
“uncommitted read” under Locking mechanism ), this can result in the
dataset selected by the SELECT command not really being written to the
database. While a program is selecting data, a second program can add,
change or delete lines at the same time. Then, the changes made by the
second program are reversed by rolling back the database system. The
selection of the first program thus reflects only a very temporary
state of the database. If such “phantom data” is not acceptable for a
program, you must either use the SAP locking mechanism or at least set
the isolation level of the database system to “committed read” (see
Locking mechanism ).
In a SELECT-ENDSELECT loop, the CONTINUE statement terminates the current loop pass prematurely and starts the next.
If
one of the statements in a SELECT … ENDSELECT loop results in a
database commit, the cursor belonging to the SELECT … ENDSELECT loop
is lost and the processing terminates with a runtime error. Since each
screen change automatically generates a database commit, statements
such as CALL SCREEN , CALL DIALOG , CALL TRANSACTION or MESSAGE are not
allowed within a SELECT … ENDSELECT loop.