SAP ABAP - Select Statement & It's Properties

Select statement

The following system fields play an important role in Open SQL operations:

SY-SUBRC

As with other ABAP/4 statements, the return code value in the system field SY-SUBRC indicates after each Open SQL operation whether or not the operation was successful. If an operation is successful, SY-SUBRC = 0. If an operation is unsuccessful – SY-SUBRC <> 0

SY-DBCNT

The value in the SY-DBCNT field indicates how many lines were affected by the operation or how many lines have already been processed.

To read data from a database table, use the SELECT command.

Syntax

SELECT FROM [INTO ] [WHERE ]. 


 This statement has several basic clauses. Each clause is described in the following table. 


The SELECT clause defines whether the result of the selection is a single line or a whole table, or few columns. 

The FROM clause specifies the database table or view from which the data is to be selected.


The INTO clause determines the target area into which the selected data is to be read. It can also be placed before the FROM clause. 


If you do not specify an INTO clause, the system uses the table work area. The table work area is a header line, which is automatically created by the TABLES statement.


The WHERE clause specifies which lines are to be read by specifying conditions for the selection. Choosing the Lines to be Read. 


 For Selecting All data from table: i.e., read all columns and all the rows from database table Syntax SELECT * FROM
(Here you are not specifying WHERE condition) Selecting All Data from a Single Line To read all columns of a single line from a database table, use the SELECT statement as follows :

Syntax
SELECT SINGLE * FROM
…… WHERE …… 


 The result of this statement is a single line. To make sure you retrieve desired unique single record, you must link all the fields which form the primary key of the database table by AND in the WHERE condition.  


Prerequisite for SELECT SINGLE 


1. Use all primary keys in WHERE condition. 
2. Always check for SY-SUBRC. 
3. Clear work-area for table.


Aggregate Expressions:

By using aggregate expressions, you can extract characteristic data from a column of the database table.

MAX: returns the maximum value of the column .

MIN: returns the minimum value of the column .

AVG: returns the average value of the column .

SUM: returns the sum value of the column .

COUNT: counts values or lines as follows: -COUNT( * ) returns the total number of lines in the selection. 

You must include spaces between the parentheses and the arguments.

The arithmetic operators AVG and SUM can only work with numeric fields.

Sometimes you retrieve few columns form database table i.e. you have list in the SELECT Clause and INTO Clause. If there is a list in the SELECT clause, you must use the INTO clause with the SELECT statement. You can use either a work area or an internal table or list of variables as an argument, 

Syntax


TABLES: SFLIGHT.
DATA : CARRIDI LIKE SFLIGHT -CARRID,
CONNID LIKE SFLIGHT –CONNID.
SELECT CARRID CONNID FROM SFLIGHT INTO (CARRID1, CONNID1). WRITE: / CARRIDl,CONNID1.
ENDSELECT.

Many times you retrieve related data from two or more tables. In such cases you use nested selects by linking tables with common primary keys. But as far as possible avoid using nested selects as time required to access nested table is very high.

Syntax

TABLES: SFLIGHT, SBOOK.
SELECT * FROM SFLIGHT WHERE CARRID = 'LH'.
SELECT * FROM SBOOK WHERE CARRID = SFLIGHT -CARRID AND
CONNID = SFLIGHT -CONNIID.
WRITE: / SFLSIGHT-CARRID,SFLIGHT-CONNID,SBOOK-BOOKID,
ENDSELECT.
ENDSELECT.

Some performance hints for Open SQL statements

• Keep the selected dataset small

Keep the number of selected data as small as possible to avoid unnecessary network transports. Use the respective Open SQL statements always with the WHERE clause. Avoid complex WHERE clauses. The system must split up those into single statements for the database system.
Do not use the logical NOT in WHERE clauses but inverted operators instead.
The logical NOT is supported by the database indexes.

• Keep the transferred data small

• Transfer only those columns of a database table that you really need. Avoid SELECT* if you do not want to read all columns of a database. Use a list in the SELECT clause instead. Use aggregate expressions in the SELECT clause to perform calculations instead transporting great amounts of data and calculating thereafter.

• Keep the number of database accesses small

• Use operations on packages of data instead of operations on single data if you want to analyze selected data more than once. To do so, transfer the data in a single operation between tables and internal tables.

• Avoid nested SELECT loops. Instead, work with internal tables and SELECT statements using the FOR ALL ENTRIES addition.