SAP NEWS-740-OPEN SQL

Get Example source ABAP code based on a different SAP table
  SAP Help

Open SQL in Release 7.40

1 Optimizing SAP buffering 2 Handling strings 3 Stricter checks on syntax rules

ABAP_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 data is read from the SAP buffer.
INTHINT Internally, a new buffer management was implemented
INTHINT that replaces the former buffer managament.
INTHINT The new buffer management is based on the
INTHINT kernel-internal handling of internal tables and can make
INTHINT use of their secondary keys.

ABAP_MODIFICATION_2 Handling Strings


The following (previously undocumented) restrictions have been lifted: Before Release 7.40, it was not possible to use DISTINCT * to read database tables containing short strings of type SSTRING. When reading LOBs before Release 7.40, unauthorized runtime errors were possible when using * to read all columns in the case of dynamically specified joins after FROM .

ABAP_MODIFICATION_3 Stricter Checks on Syntax Rules


In Release 7.40, 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. One consequence of this is that any following syntax constructs that have always contained errors now produce syntax errors or runtime errors.

General Corrections

Before Release 7.40, 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, this situation produces a syntax check warning.

Example

From Release 7.40, 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 = '...'.

Before Release 7.40, 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, a period like this raises an exception of the class CX_SY_DYNAMIC_OSQL_SYNTAX.

Example

From Release 7.40, an exception for:

SELECT *
FROM (`SPFLI .`)
INTO TABLE itab
WHERE (`. CARRID = 'LH'`).

The addition GROUP BY cannot be specified for pooled tables and cluster tables. Before Release 7.40, it was possible to specify a column dynamically after GROUP BY; however this always raised an exception. From Release 7.40, 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, a syntax warning or error for:

SELECT id object langu typ
FROM doktl
INTO TABLE itab
GROUP BY (`ID OBJECT LANGU TYP`).

The addition WITH HOLD of the statement OPEN CURSOR can be used only in reads performed on the standard database. If the addition CONNECTION is specified at the same time, a runtime error was produced before Release 7.40 (and not a syntax error), if the database table was specified dynamically. This gap was closed in Release 7.40.

Example

From Release 7.40, syntax errors for:

OPEN CURSOR WITH HOLD cursor
FOR SELECT *
FROM ('SPFLI') CONNECTION con.

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, 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, the target field must be numeric and a loss of values always produces an exception.

Example

From Release 7.40, 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 after SELECT, 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, 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, this also produces a syntax error (if statically identifiable).

Example

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

Example

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

Example

From Release 7.40, 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, columns of this type read without length fields produced a syntax warning. From Release 7.40, this situation always produces a runtime error.

Example

From Release 7.40, 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, the subquery was not checked. From Release 7.40, the comparison must be specified (statically or dynamically) even if a subquery is specified.

Example

From Release 7.40, 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, this was not identified correctly for locators, either statically or at runtime. From Release 7.40, this produces a syntax error or raises an exception.

Example

From Release 7.40, 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, this situation produces a syntax check warning.

Example

From Release 7.40, a 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, 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, this text produced a syntax warning; from Release 7.40, it produces a syntax error..

Example

From Release 7.40, a 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, a syntax warning is produced in this case, if statically identifiable; if not, an exception is raised.

Example

From Release 7.40, 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 listed 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, the checks on this situation at runtime were not strict enough and the behavior was platform-dependent. From Release 7.40, a violation of this rule always raises an exception of the class CX_SY_DYNAMIC_OSQL_SEMANTICS.

Example

From Release 7.40, 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 column specified after SELECT cannot be the name of a column to which no alternative column name is assigned. Before Release 7.40, the use of a name of this type after ORDER BY raised an exception. From Release 7.40, this also produces a syntax error (if statically identifiable).

Example

From Release 7.40, syntax errors for:

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