ABAP FROM CLAUSE Statement syntax and functionality in SAP

SELECT - FROM

Short Reference

ABAP Syntax ... FROM { {dbtab [AS tabalias]}
| join
| {(dbtab_syntax) [AS tabalias]} }
[CLIENT SPECIFIED]
[UP TO n ROWS]
[BYPASSING BUFFER]
[ CONNECTION con|(con_syntax) ] ... .

ABAP_ALTERNATIVES:
1 ... dbtab [AS tabalias]
2 ... join
3 ... (dbtab_syntax) [AS tabalias]

ABAP_ADDITIONS:
1 ... CLIENT SPECIFIED
2 ... UP TO n ROWS
3 ... BYPASSING BUFFER

What does it do? Entries in source specify whether a database table , a view , or multiple database tables or views are accessed by a join expression . Optional additions perform client handling , specify whether SAP buffering is bypassed, and determine the maximum number of rows to be read.

ABAP_ALTERNATIVE_1 ... dbtab [AS tabalias]

What does it do? A database table or view defined in ABAP Dictionary can be specified for dbtab . An alternative table name tabalias
can be assigned to the database table or the view using the addition AS . This name is valid during the SELECT statement only, and in all other positions where this specified database table is addressed, and the actual name does not need to be used.
Latest notes: If a database table or a view appears multiple times after
FROM in a join expression, the alternative name must be used to avoid ambiguities.
Example ABAP Coding Reading from the database table spfli and assigning the alternative name s . In this case, the prefix
s~ after ORDER BY also does not need to be specified, because only one database table is read and the column name carrid
is unique. The prefix spfli~ can no longer be used when assigning the alternative name.
DATA wa TYPE spfli.

SELECT *
FROM spfli AS s
INTO wa
ORDER BY s~carrid.
cl_demo_output=>write( wa ).
ENDSELECT.
cl_demo_output=>display( ).

ABAP_ALTERNATIVE_2 ... join

What does it do? Specifies a join expression that joins multiple database tables or views with one another.

ABAP_ALTERNATIVE_3 ... (dbtab_syntax) [AS tabalias]

What does it do? Instead of making static specifications, a data object dbtab_syntax can be specified in parentheses. When executing the statement, it must contain the syntax displayed when specified statically. The data object dbtab_syntax can be a character-like data object or a standard table without secondary table keys and with a character-like data object. The syntax in dbtab_syntax is, as in ABAP Editor, not case-sensitive. When an internal table is specified, the syntax can be distributed across multiple rows.
The addition AS can be specified only if dbtab_syntax contains only the name of a single database table or a view. The addition has the same meaning for this database table or view as when specified statically.
When specifying the syntax in dbtab_syntax , the following restrictions apply:
Only a list of fields can be specified in a join condition after the language element IN , not a
selection table .
No database table containing columns of the type RAWSTRING , SSTRING , or STRING can be used in a join expression. See SQL Injections Using Dynamic Tokens .
Latest notes: If dbtab_syntax is an internal table with a header line , the header line and not the table body is evaluated.
The class CL_ABAP_DYN_PRG contains methods that support the creation of correct and secure dynamic database table specifications.
Example ABAP Coding Displaying the flight connections (flight date, airline name, and flight number) for the user input of a departure city and a destination city. The inner joins are constructed dynamically at runtime. The column specified after
SELECT is also dynamic. The values entered on the selection screen are specified dynamically using the name of the parameter in question. They are not chained directly. If they were, a special security check would be required for these parameters.
PARAMETERS: p_cityfr TYPE spfli-cityfrom,
p_cityto TYPE spfli-cityto.

DATA: BEGIN OF wa,
fldate TYPE sflight-fldate,
carrname TYPE scarr-carrname,
connid TYPE spfli-connid,
END OF wa.

DATA itab LIKE SORTED TABLE OF wa
WITH UNIQUE KEY fldate carrname connid.
DATA: column_syntax TYPE string,
dbtab_syntax TYPE string.

column_syntax = `c~carrname p~connid f~fldate`.

dbtab_syntax = `( ( scarr AS c `
<(> <)> ` INNER JOIN spfli AS p ON p~carrid = c~carrid`
<(> <)> ` AND p~cityfrom = p_cityfr`
<(> <)> ` AND p~cityto = p_cityto )`
<(> <)> ` INNER JOIN sflight AS f ON f~carrid = p~carrid `
<(> <)> ` AND f~connid = p~connid )`.

SELECT (column_syntax)
FROM (dbtab_syntax)
INTO CORRESPONDING FIELDS OF TABLE itab.

cl_demo_output=>display_data( itab ).

Example ABAP Coding See SELECT , Dynamically Specified Tokens

ABAP_ADDITION_1 ... CLIENT SPECIFIED

What does it do? This addition deactivates automatic client handling in Open SQL . When specifying a single database table or a single view, the addition must be inserted directly after dbtab in the join condition. When specifying a join expression, it must be inserted after the last addition ON of the join condition.
When using the addition CLIENT SPECIFIED , the first column of the client-specific database tables can be specified in the WHERE
condition to determine the client identifier . In the addition ORDER BY , the column can be sorted explicitly according to client identifier.

Latest notes: If the addition CLIENT SPECIFIED is specified, the client column is handled like any other column in the table. If the client ID is not specified in the WHERE condition, the selection is made across all clients.

If the addition CLIENT SPECIFIED is specified, but the client ID in the WHERE condition is not, the SELECT statement bypasses SAP buffering .

Since each client represents a complete unit, automatic client handling should never be turned off in application programs. This is checked by the ABAP runtime environment in
multitenancy systems.

(See also Cross-Client Database Access
).

Example ABAP Coding Reading all customers in client "800".
DATA wa_scustom TYPE scustom.

SELECT *
FROM scustom CLIENT SPECIFIED
INTO wa_scustom
WHERE mandt = '800'.
ENDSELECT.

ABAP_ADDITION_2 ... UP TO n ROWS

What does it do? This addition restricts the number of rows in the result set. n expects a data object of type i that can contain all non-negative numbers from the value range except its maximum value +2.147.483.647 . A positive number in n indicates the maximum number of rows in the result set. If n contains the value 0, all selected rows are passed to the result set. If n contains a negative number or +2.147.483.647 , a syntax error is produced or a non-handleable exception is raised.
Latest notes: The addition UP TO n ROWS should 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 useful for finding out whether a database table contains any rows at all.
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 result set. If the addition ORDER BY is not specified, n
arbitrary rows that meet the WHERE condition are passed to the result 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.
Example ABAP Coding Reading the three business customers with the highest discount rates:
DATA: wa_scustom TYPE scustom.

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

ABAP_ADDITION_3 ... BYPASSING BUFFER

What does it do? 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

ABAP FROM CLAUSE Statement syntax, information and example SAP source code