SELECT - columns
Short Reference
ABAP Syntax ... *
| { {col1| aggregate( [DISTINCT] col1 )
} [AS a1]
{col2| aggregate( [DISTINCT] col2 )
} [AS a2] ... }
| (column_syntax) ... .
ABAP_ALTERNATIVES:
1 ... *
2 ... {col1|aggregate( [DISTINCT] col1 )} [AS a1]
{col2|aggregate( [DISTINCT] col2 )} [AS a2] ...
3 ... (column_syntax)
What does it do? The data in columns determines which columns are
used to construct the results set.
ABAP_ALTERNATIVE_1 ... *
What does it do? If * is specified, the results set is constructed
based on all columns in the database
tables or views specified after
FROM , in the order given there. The
columns in the results set inherit the name and data type from the
database tables or views. Only one data object can be specified after
INTO .
Latest notes: If multiple database tables are specified after
FROM
, it is not possible to prevent multiple columns from inheriting the
same name when * is specified.
For performance reasons, only specify those columns that are actually
needed. Specify * only if the content of all columns is actually
needed.
Example ABAP Coding Reads all columns of multiple rows.
DATA wa TYPE spfli.
SELECT *
FROM spfli
INTO CORRESPONDING FIELDS OF wa
WHERE carrid = 'LH'.
ENDSELECT.
ABAP_ALTERNATIVE_2 ... {col1|aggregate( [DISTINCT] col1 )} [AS
a1]
{col2|aggregate( [DISTINCT] col2 )} [AS a2] ...
What does it do? A list of column labels col1 col2 ... is specified
in order to construct the results list from individual columns. An
individual column can be specified directly or as an argument of an
aggregate function aggregate . The
order in which the column labels are specified is free and defines the
order of the columns in the results list. Only if a column of the type
LCHAR or LRAW is listed does the corresponding length
field also have to be specified directly before it. An individual column
can be specified more than once. Various specifications can be made
after INTO , and their interaction is
described by the column specified there.
The addition AS can be used to define an alternative column
name a1 a2 ... (with a maximum of thirty characters) in the
results set for every column label col1 col2 ... . An alternative
column name cannot be assigned more than once. You should also not use
the name of column that does not have any alternative names assigned to
it. The system uses the alternative column name in the additions
INTO|APPENDING CORRESPONDING FIELDS and
ORDER BY . A non-unique column name
used after ORDER BY causes a syntax error or an exception.
Column Labels
The following column labels are possible:
If only a single database table or a
single view is specified after
FROM , the column labels in the database
table (that is, the names of the components comp1 comp2... ) can
be specified directly for col1 col2 ... in the structure from
ABAP Dictionary.
If the name of the component appears in multiple database tables of
the addition FROM and the required database table or view
dbtab is specified only once after FROM , the names
dbtab~comp1 dbtab~comp2 ... must be specified for col1 col2 ...
. Here, comp1 comp2 ... are the names of the components in the
structure from ABAP Dictionary and ~ is the
column selector .
If the required database table or view occurs multiple times after
FROM , the names tabalias~comp1 tabalias~comp2 ... have to
be specified for col1 col2 ... . tabalias is the
alternative table name of the database table or view defined after
FROM , and comp1 comp2 ... are the names of the components in
the structure from ABAP Dictionary and ~ is the
column selector .
The data type of a single column in the results list is the data type
of the corresponding component in ABAP Dictionary. The corresponding
data object after INTO or APPENDING has to be selected
accordingly.
Latest notes: If multiple database tables are specified after FROM
, alternative names can be used when specifying single columns to
avoid having multiple columns with the same name.
If the value of a column of type LRAW or LCHR is read,
the associated length field of type INT2 must also be read and
specified in the list in front of the column of type LRAW or
LCHR .
The obsolete short form without an
explicitly specified target area cannot be used when specifying
individual columns. The only exception here is when count( * ) is
used to specify "nothing", if no alternative column name and no
GROUP BY have been specified.
Example ABAP Coding Produces the flight date and the average booking rate of
all customers of Lufthansa flights with the flight number 0400. The
alternative name avg of the aggregate expression is required to
sort the results set and assign it to the
columns of the target table using CORRESPONDING
FIELDS .
TYPES: BEGIN OF wa,
fldate TYPE sbook-fldate,
avg TYPE sbook-loccuram,
END OF wa.
DATA itab TYPE TABLE OF wa WITH EMPTY KEY.
SELECT fldate AVG( loccuram ) AS avg
INTO CORRESPONDING FIELDS OF TABLE itab
FROM sbook
WHERE sbook~carrid = 'LH' AND
sbook~connid = '0400'
GROUP BY fldate
ORDER BY avg DESCENDING.
cl_demo_output=>display( itab ).
ABAP_ALTERNATIVE_3 ... (column_syntax)
What does it do? Instead of static data, a data object column_syntax
in parentheses can be specified, which, when the command is
executed, either contains the syntax shown with the static data, or is
initial. The data object column_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 column_syntax ,
as ABAP Editor, is not case-sensitive. When an internal table is
specified, the syntax can be distributed across multiple rows.
If column_syntax is initial when the command is executed,
columns is implicitly set to * and all columns are read.
If columns are specified dynamically without the SINGLE
addition, the results set is always regarded as having multiple rows.
See SQL Injections Using Dynamic
Tokens .
Latest notes: When pooled tables or
cluster tables are accessed
dynamically, the use of the DISTINCT addition raises a handleable
exception.
If column_syntax is an internal table with a
header line , the
table body is evaluated, and not the
header line.
The class CL_ABAP_DYN_PRG
contains methods that support the creation of correct and secure dynamic
column specifications.
Example ABAP Coding Produces all departure or destination cities of
Lufthansa flights, depending on whether 'CITYFROM' or
'CITYTO' is specified. A method of the class
CL_ABAP_DYN_PRG is used to check
whether the input values are valid.
DATA: comp TYPE c LENGTH 16,
dref TYPE REF TO data.
cl_demo_input=>request( CHANGING field = comp ).
TRY.
comp =
cl_abap_dyn_prg=>check_whitelist_tab(
val = to_upper( comp )
whitelist = VALUE string_hashed_table( ( `CITYFROM` )
( `CITYTO` ) ) ).
CATCH cx_abap_not_in_whitelist.
cl_demo_output=>display( 'Not allowed' ).
LEAVE PROGRAM.
ENDTRY.
DATA(long_name) = `SPFLI-` <(> <)><(> <)> comp.
CREATE DATA dref TYPE (long_name).
ASSIGN dref->* TO FIELD-SYMBOL(<(><<)>fs>).
DATA output TYPE TABLE OF string WITH EMPTY KEY.
SELECT DISTINCT (comp)
INTO <(><<)>fs>
FROM spfli
WHERE carrid = 'LH'.
APPEND |{ <(><<)>fs> }| TO output.
ENDSELECT.
cl_demo_output=>display( output ).
Example ABAP Coding See SELECT , dynamic
token specification
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved