Table buffering has been optimized so that, if the database table is specified statically, its
secondary indexes are also respected when data is read from the
table buffer (in cases where generic buffering or full buffering is activated).
If SELECT is used with FOR ALL ENTRIES,
table buffering is now also used when accessing tables with single record buffering and is no longer bypassed.
Modification 2
Result Type of the Aggregate Function COUNT( * )
In cases where the aggregate function COUNT( *
) or COUNT(*)
is specified as the only element in the SELECT list and without a GROUP
BY clause, INT8 was added to the internal data type of the result. If the value range is to be
used in full, a target object with the data type p or decfloat34
must be used after INTO. The
system field sy-dbcnt is set to the value -1 in events outside its value range.
Modification 3
Decimal Places in the INTO Clause
The assignment rules of the INTO clause of the statement
SELECT were modified so that surplus decimal places are now always cut off when numbers are
assigned to target fields with too few decimal places. Until now, it was possible to round the numbers (depending on the database and table buffering).
Modification 4
Conditions in Outer Joins
The restriction in previous versions, which meant that only equality comparisons (=, EQ) were possible in the ON condition of
outer joins, no longer applies.
Modification 5
Enhancements for Sorting by Primary Key
If the addition PRIMARY KEY is used after ORDER BY, the following restrictions no longer apply:
If individual columns are specified in the SELECT list, it is not necessary to specify the
client
column explicitly if the addition DISTINCT is used.
A view can also be specified statically after FROM, provided that the view contains fewer key fields than view fields.
The addition PRIMARY KEY can now also be specified dynamically.
Modification 6
Handling Strings
The following (previously undocumented) restrictions have been lifted:
Before Release 7.40 SP02, it was not possible to use DISTINCT * to read database tables containing short strings of type SSTRING.
Before Release 7.40, SP02, it was not possible to access database tables with short or long strings
of the data types SSTRING, STRING, or RAWSTRING using * in the SELECT list in cases where a join is specified dynamically after FROM.
Modification 7
Field Symbols and Data Reference Variables in SELECT Loops
When field symbols or dereferenced reference variables are specified for the work area, individual data
objects, or internal tables in a SELECT loop after INTO, the data object that is the target of a field symbol or reference
variable is identified precisely once, from Release 7.40, SP02, when the loop is entered. This data
object is used as a target area in each loop pass. Any modifications to the assignment of a field symbol
or reference variable within the loop are ignored. From Release 7.40, SP02, the assignment of a field
symbol or reference variable is determined again for each loop pass and the current data object is used as the target area.
Modification 8
Specifying dynamic tokens
With Release 7.40 SP02 and higher, internal tables, which are specified as dynamic tokens of statement
SELECT, can also have secondary keys.
Modification 9
Stricter Checks on Syntax Rules
In Release 7.40 SP02, a new SQL parser was introduced for Open SQL. These parser performs stricter checks
on some rules than the old parser. More specifically, the same parser is now used for statically specified
Open SQL and for the content of dynamic tokens. In Release 7.40, SP02, this parser will initially only
be used for the statement SELECT.
One consequence of this is that any following syntax constructs that have always contained errors now produce syntax errors or runtime errors.
Before Release 7.40, SP02, the operator
IN seltab of a WHERE condition was not always checked statically
to see whether the columns LOW and HIGH of the selection table seltab could
be converted to the data type of the database and non-convertible columns did not produce a runtime
error in cases where the selection table was empty. Now, a static check is always made a non-convertible columns always raise an exception.
Example
From Release 7.40 SP02, syntax errors for:
DATA: range_tab TYPE RANGE OF t,
itab TYPE TABLE OF sflight.
SELECT * FROM sflight
INTO TABLE itab WHERE fldate IN range_tab.
Before Release 7.40, SP02, multiple NOT
operators could be placed consecutively in a WHERE condition. An even or
odd number of consecutive NOTs is the same as no NOTs
or a single NOT, which means that surplus NOTs can now no longer be specified.
Example
From Release 7.40 SP02, syntax errors for:
SELECT SINGLE *
FROM spfli INTO wa WHERE NOT NOT carrid = 'LH'.
Before Release 7.40 SP02, it was possible to read the
client column when using
alternative table names or
joins in ON and WHERE conditions, without deactivating automatic
client handling using
CLIENT SPECIFIED. In this
case, the results set is empty whenever the explicitly specified client is not the current client. From Release 7.40 SP02, this situation produces a syntax check warning.
Example
From Release 7.40 SP02, syntax warnings for:
SELECT *
FROM scarr AS carriers INTO TABLE itab
WHERE carriers~mandt = '...'.
and
SELECT *
FROM scarr INNER JOIN spfli
on scarr~mandt = spfli~mandt INTO CORRESPONDING FIELDS OF TABLE itab
WHERE scarr~mandt = '...'.
The addition GROUP BY cannot be specified for
pooled tables and
cluster tables. Before
Release 7.40 SP02, it was possible to specify a column dynamically after GROUP BY;
however this always raised an exception. From Release 7.40 SP02, a dynamically specified GROUP
BY clause in pooled tables and cluster tables produces a syntax warning; this warning will become a syntax error in a future SP.
Example
From Release 7.40 SP02, a syntax warning or error for:
SELECT id object langu typ FROM doktl
INTO TABLE itab GROUP BY (`ID OBJECT LANGU TYP`).
Corrections for Dynamic Tokens
Before Release 7.40 SP02, a single period (.) could be specified in the
dynamic tokens of any Open SQL statements. This period was ignored when the token was evaluated at runtime.
From Release 7.40 SP02, a period like this raises an exception of the class CX_SY_DYNAMIC_OSQL_SYNTAX.
Example
From Release 7.40 SP02, exception for:
SELECT *
FROM (`SPFLI .`) INTO TABLE itab WHERE (`. CARRID = 'LH'`).
Before Release 7.40, SP02, an alias name could be given more than once in cases where columns were
specified dynamically in the SELECT
list of the columns after SELECT using column_syntax,
even though this is not allowed statically. From Release 7.40 SP02, this raises an exception of the class CX_SY_DYNAMIC_OSQL_SEMANTICS.
Example
From Release 7.40 SP02, exception for:
SELECT SINGLE ('carrid AS col carrname AS col')
FROM scarr INTO CORRESPONDING FIELDS OF wa WHERE carrid = 'LH'.
Before Release 7.40, SP02 the statically compulsory addition DISTINCT
could be omitted when the aggregate function COUNT( DISTINCT col
) was specified dynamically and all rows of the results set were counted. From Release 7.40
SP02, the omission of DISTINCT raises an exception of the class CX_SY_DYNAMIC_OSQL_SYNTAX.
Example
From Release 7.40 SP02, exception for:
SELECT ('COUNT( carrid )')
FROM spfli INTO count. ENDSELECT.
In previous releases (before 7.40 SP02), a NOT could be mistakenly written
directly in front of a relational operator in a dynamic WHERE condition (which
is not possible in the static case). With Release 7.40 SP02 and higher, this raises an exception of class CX_SY_DYNAMIC_OSQL_SYNTAX.
Example
From Release 7.40 SP02, exception for:
SELECT SINGLE *
FROM spfli INTO wa WHERE (`carrid NOT = 'LH'`).
Corrections for the aggregate function count( * )
As in all aggregate functions, the target field must be chosen appropriately in the case of count( *
) or count(*)
and no values must be lost when the result is assigned. This was not checked before Release 7.40 SP02,
and assignments were made in accordance with the conversions rules. This did not always raise an exception
when values were lost. From Release 7.40 SP02, the target field must be numeric and a loss of values always produces an exception.
Example
From Release 7.40 SP02, a syntax warning and exception (if the value does not fit in the target field) for
DATA cnt TYPE c LENGTH 1.
SELECT COUNT(*) FROM scarr INTO cnt.
When individual columns or aggregate functions
are specified in the SELECT list,
an explicit work area must usually be specified and the obsolete short form is not possible. The only exception here is when count( *
) is used to specify "nothing", if no alternative column name and no GROUP BY have been specified. Before Release 7.40 SP02, the short form using count( *
), specified together with an alternative column name or a GROUP BY
clause, produced a runtime error. From Release 7.40 SP02, this also produces a syntax error if known statically.
Example
From Release 7.40 SP02, syntax errors for:
TABLES scarr.
SELECT COUNT( * ) AS cnt FROM scarr.
SELECT count( * )
FROM scarr GROUP BY carrid. ... ENDSELECT.
Corrections when using the predefined types LCHR and LRAW from ABAP Dictionary.
Columns of the types LCHR and LRAW cannot be used in comparisons of the
SQL conditions. Before Release 7.40 SP02, this produced
a runtime error. From Release 7.40 SP02, this also produces a syntax error if known statically.
Example
From Release 7.40 SP02, syntax errors for:
SELECT SINGLE *
FROM indx INTO wa WHERE clustd = '...'.
Columns of the types LCHR and LRAW cannot be read using SELECT if the
addition DISTINCT is specified.
Before Release 7.40 SP02, this produced a runtime error. From Release 7.40 SP02, this also produces a syntax error if known statically.
Example
From Release 7.40 SP02, syntax errors for:
SELECT DISTINCT *
FROM indx INTO TABLE itab.
Columns of the types LCHR and LRAW can be read using SELECT only if they
are read together with the associated length fields. Before Release 7.40 SP02, columns of this type
read without length fields produced a syntax warning. From Release 7.40 SP02, this situation always produces a runtime error.
Example
From Release 7.40 SP02, runtime errors for:
SELECT clustd
FROM indx INTO TABLE itab.
Corrections for FOR ALL ENTRIES
If FOR ALL ENTRIES
is used in front of a WHERE condition of a SELECT statement, a column of the internal table must be specified in at least one comparison (the comparison can also be specified in a
subquery). Before Release
7.40 SP02, the subquery was not checked. From Release 7.40 SP02, the comparison must be specified (statically or dynamically) even if a subquery is specified.
Example
From Release 7.40 SP02, syntax errors for:
SELECT carrid connid fldate
FROM sflight INTO CORRESPONDING FIELDS OF TABLE rtab
FOR ALL ENTRIES IN itab WHERE EXISTS ( SELECT * FROM sflight ).
When FOR ALL ENTRIES
is used in front of a WHERE condition of a SELECT
statement, no database fields of the predefined types STRING and RAWSTRING plus LCHR and LRAW can occur
in the SELECT list, since
the implicit addition DISTINCT cannot be passed to the database system in this case. From Release 7.40, SP02, a syntax warning occurs in the
extended program check. This warning can be hidden by a pragma.
Example
From Release 7.40 SP02, pragma required for:
SELECT *
FROM snwd_bpa INTO TABLE bupas
FOR ALL ENTRIES IN orders WHERE node_key = orders-buyer_guid
##select_fae_with_lob[web_address].
If FOR ALL ENTRIES is used in front of a WHERE condition of a SELECT statement, no
LOB handles can be created in the target area, since this produces an undefined result. Before Release 7.40 SP02, this was not identified correctly for
locators, either statically or at runtime. From Release 7.40 SP02, this produces a syntax error or raises an exception.
Example
From Release 7.40 SP02, syntax errors for:
SELECT picture
FROM demo_blob_table INTO wa-picture
FOR ALL ENTRIES IN name_tab WHERE name = name_tab-table_line. ENDSELECT.
The addition FOR ALL ENTRIES
should not be used with the addition GROUP
BY. The addition GROUP BY is ignored if used together with
FOR ALL ENTRIES. From Release 7.40 SP02, this situation produces a syntax check warning.
Example
From Release 7.40 SP02, syntax warning for:
SELECT COUNT( * )
FROM spfli INTO cnt FOR ALL ENTRIES IN carriers
WHERE carrid = carriers-table_line GROUP BY carrid.
Corrections for ORDER BY
Before Release 7.40 SP02, it was possible to specify any text between a dynamically specified column
after ORDER BY and the
closing period of a SELECT statement and this text was ignored when the statement
was executed. Before Release 7.40 SP02, this text produced a syntax warning; from Release 7.40 SP02, it produces a syntax error..
Example
From Release 7.40 SP02, syntax warning for:
SELECT *
FROM scarr INTO TABLE itab ORDER BY (`CARRID`) carrname and so on.
If the addition ORDER BY
is specified together with FOR
ALL ENTRIES, all columns of the primary key must be read; if not, the result is undefined.
From Release 7.40 SP02, a syntax warning is produced in this case if known statically; at runtime, an exception is always raised.
Example
From Release 7.40 SP02, a syntax warning or exception for:
SELECT carrid connid FROM sflight
INTO CORRESPONDING FIELDS OF TABLE rtab FOR ALL ENTRIES IN itab
WHERE carrid = itab-carrid AND connid = itab-connid
ORDER BY PRIMARY KEY.
If aggregate functions
are specified after SELECT, all columns that are specified after ORDER BY and that do not have an alternative column name for an aggregation
function must also be specified after SELECT and after GROUP
BY. Before Release 7.40 SP02, the checks on this situation at runtime were not strict enough
and the behavior was platform-dependent. From Release 7.40 SP02, a violation of this rule always raises an exception of the class CX_SY_DYNAMIC_OSQL_SEMANTICS.
Example
From Release 7.40 SP02, an exception from the class CX_SY_DYNAMIC_OSQL_SEMANTICS for:
SELECT COUNT( * ) FROM spfli INTO (cnt)
GROUP BY ('CARRID') ORDER BY ('CARRID'). ... ENDSELECT.
An alternative column name in the SELECT
list cannot be the name of a column to which no alternative column name is assigned. Before Release
7.40 SP02, the use of a name of this type after
ORDER BY raised an exception. From Release 7.40 SP02, this also produces a syntax error if known statically.
Example
From Release 7.40 SP02, syntax errors for:
SELECT carrid connid AS carrid
FROM spfli INTO TABLE itab ORDER BY carrid.