Skip to content

ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Open SQL →  Open SQL - Read Accesses →  SELECT 

SELECT - additions

Short Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax


... [UP TO n ROWS] 
    [BYPASSING BUFFER] 
    [CONNECTION con|(con_syntax)] ...

Extras

1.... UP TO n ROWS

2.... BYPASSING BUFFER

3.... CONNECTION con|(con_syntax)

Effect

These optional additions to statement SELECT specify whether SAP buffering is bypassed, specify the maximum number of rows to be read and define the database connection.

If the INTO clause is specified as last clause of the SELECT statement, the additions must follow after the INTO clause. Otherwise, they can also be specified after the SELECT clause or after the FROM clause.

Addition 1

... UP TO n ROWS

Effect

This addition restricts the number of rows in the results set of a SELECT statement. n expects a host variable or literal of type i that can contain all non-negative numbers from the value range except its maximum value +2,147,483,647. A host variable should be prefixed by the escape character @. The content of n must match the data type i in accordance with the rules for a lossless assignment.

A positive number in n indicates the maximum number of rows in the results set. If n contains the value 0, all selected rows are passed to the results set. If n contains a negative number or +2.147.483.647, a syntax error is produced or a non-handleable exception is raised.


Notes

  • The addition UP TO n ROWS should be used in preference to a SELECT loop that is canceled after importing n rows. In the latter case, the last package passed from the database to the application server usually contains superfluous rows.
  • The addition UP TO 1 ROWS is often used to confirm whether a database table contains any rows that meet a certain condition at all. To avoid unnecessary transports of data, a SELECT list can also be used that contains nothing but a single constant (see Example).
  • If the addition ORDER BY is also specified, the rows of the hit list are sorted on the database server and only the number of sorted rows specified in n are passed to the results set. If the addition ORDER BY is not specified, n arbitrary rows that meet the WHERE condition are passed to the results set.
  • If the addition FOR ALL ENTRIES is also specified, all selected rows are initially read into a system table and the addition UP TO n ROWS only takes effect during the passing from the system table to the actual target area. This can result in unexpected memory bottlenecks.
  • Host variables without the escape character @ are obsolete. The escape character @ must be specified in the strict modes of the syntax check from Release 7.40, SP05.


Example

Reading the three business customers with the highest discount rates:

DATA: wa_scustom TYPE scustom. 

SELECT * 
       FROM scustom 
       WHERE custtype = 'B' 
       ORDER BY discount DESCENDING 
       INTO @wa_scustom 
       UP TO 3 ROWS. 
ENDSELECT.

Addition 2

... BYPASSING BUFFER

Effect

This addition causes the SELECT statement to bypass SAP buffering and to read directly from the database and not from the buffer on the application server

Addition 3

... CONNECTION con|(con_syntax)

Effect

The Open SQL command is not executed on the standard database connection but on the specified secondary database connection. The database connection can be specified statically with con or dynamically as the content of con_syntax, where the field con_syntax must belong to the type c or string. The database connection must be specified with a name, which is contained in the table DBCON in the column CON_NAME or which begins with prefix R/3* and therefore represents a service connection for the standard database.

The database tables or views specified in the current Open SQL statement must be active in ABAP Dictionary in the current AS ABAP regardless of the specified database connection. Only transparent tables can be specified as database tables. Pooled tables and cluster tables cannot be specified together with the addition CONNECTION. In the secondary database, an identically named and usable object with a suitable structure must exist for each database table or view specified in the current Open SQL statement. If not, an exception is raised.


Notes

  • Secondary database connections can be used to access all views that can be accessed using Open SQL, namely database views, projection views, external views, and CDS views. CDS views can be specified using the name of the CDS entity and the CDS database view.
  • The type of a database object specified in an Open SQL statement does not necessarily need to match the type of the database object with the same name in the secondary database. For example, a view with the same name in the secondary database can be accessed by specifying a database table (or a database table by specifying a view) if they have the same structure.
  • Entries in the database table DBCON can only be created and modified using the DBA Cockpit tool.
  • The addition CONNECTION cannot be used together with the addition WITH HOLD of the statement OPEN CURSOR.


Example

Reads data using a service connection to the standard database.

SELECT * 
       FROM scarr CONNECTION r/3*my_conn 
       INTO TABLE @DATA(itab). 

cl_demo_output=>display( itab ).