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.
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 LOB s 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 handle s 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.
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved