ABAP Keyword Documentation → ABAP - Release-Specific Changes → Changes in Release 7.40 → Changes in Release 7.40, SP02

Open SQL in Release 7.40, SP02

1. Optimizing SAP buffering

2. Result type of the aggregate function COUNT( * )

3. Conditions with outer joins

4. Enhancements for sorting by primary key

5. Handling strings

6. Field symbols and data reference variables in SELECT loops

7. Stricter checks for syntax rules

Other versions: ~~7.31~~ | 7.40 | 7.54

Modification 1

Optimizing SAP Buffering

SAP buffering has been optimized so that, if the database table is specified statically, its secondary indexes are also respected when generic buffering or full buffering is activated.
If SELECT is used with FOR ALL ENTRIES, SAP 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

Conditions with 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 4

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.

Modification 5

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.
When reading LOBs in releases before 7.40 SP02, unauthorized runtime errors were possible when using * in the SELECT list to read all columns in the case of dynamically specified joins after FROM.

Modification 6

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 7

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.

General corrections

From Release 7.40, SP02, the content of the operand n of the additions UP TO n ROWS and PACKAGE SIZE of the statement SELECT meet the rules of a lossless assignment for the data type i.

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. Since an even or uneven number of consecutive NOTs is the same as no NOTs or a single NOT, 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 statically identifiable).

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 WHERE condition. Before Release 7.40 SP02, this produced a runtime error. From Release 7.40 SP02, this also produces a syntax error (if statically identifiable).

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 statically identifiable).

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 ).

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 statically identifiable; 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 statically identifiable).

Example

From Release 7.40 SP02, syntax errors for:

SELECT carrid connid AS carrid
       FROM spfli
       INTO TABLE itab
       ORDER BY carrid.