SAP NEWS-740-ABAP SQL



Get Example source ABAP code based on a different SAP table
  



AB_SQL in ABAP Release 7.40, SP02
ITOC

ABAP_MODIFICATION_NN Optimized Table Buffering

The following improvements were made:
Table buffering was 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.
BEGIN_SECTION SAP_INTERNAL_HINT
Internally, a new buffer management was implemented that replaces the former buffer management. The new buffer management is based on the kernel-internal handling of internal tables and can make use of their secondary keys. In the new buffer management, the buffer is not bypassed any more when isolation level committed read is set. Before, the buffer had to be bypassed during committed read in order to prevent the selection of phantom data. This bypassing during committed read was not documented.
END_SECTION SAP_INTERNAL_HINT

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

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

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

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

ABAP_MODIFICATION_NN Handling Strings

The following (previously undocumented) restrictions were lifted:
Before ABAP_RELEASE 7.40 SP02, it was not possible to use DISTINCT * to read database tables containing short strings of type SSTRING.
Before ABAP_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.

ABAP_MODIFICATION_NN 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 exactly once, from ABAP_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 ABAP_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.

ABAP_MODIFICATION_NN Specifying Dynamic Tokens

With ABAP_RELEASE 7.40 SP02 and higher, internal tables, which are specified as dynamic tokens of statement SELECT , can also have secondary keys.

ABAP_MODIFICATION_NN Stricter Checks on Syntax Rules

In ABAP_RELEASE 7.40 SP02, a new SQL parser was introduced for AB_SQL . These parser performs stricter checks on some rules than the old parser. More specifically, the same parser is now used for statically specified AB_SQL and for the content of dynamic tokens. In ABAP_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 ABAP_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 ABAP_RELEASE 7.40, SP02, the operator IN range_tab of a WHERE condition was not always checked statically to see whether the columns LOW and HIGH of the ranges table range_tab could be converted to the data type of the database and non-convertible columns did not produce a runtime error in cases where the ranges table was empty. Now, a static check is always made a non-convertible columns always raise an exception.
Example
From ABAP_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 ABAP_RELEASE 7.40, SP02, multiple NOT operators could be placed consecutively in a WHERE condition. An even or odd number of consecutive NOT operators is the same as no NOT or a single NOT, which means that surplus NOT operators can now no longer be specified.
Example
From ABAP_RELEASE 7.40 SP02, syntax errors for:
SELECT SINGLE *
FROM spfli
INTO wa WHERE
NOT NOT carrid = 'LH'.
Before ABAP_RELEASE 7.40 SP02, it was possible to read the client column when using alias names defined with AS or joins in ON and WHERE conditions, without disabling implicit client handling using CLIENT SPECIFIED . In this case, the result set is empty whenever the explicitly specified client is not the current client. From ABAP_RELEASE 7.40 SP02, this situation produces a syntax check warning.
Example
From ABAP_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 ABAP_RELEASE 7.40 SP02, it was possible to specify a column dynamically after GROUP BY ; however this always raised an exception. From ABAP_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 ABAP_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 ABAP_RELEASE 7.40 SP02, a single period (.) could be specified in the dynamic tokens of any AB-SQL statements. This period was ignored when the token was evaluated at runtime. From ABAP_RELEASE 7.40 SP02, a period like this raises an exception of the class CX_SY_DYNAMIC_OSQL_SYNTAX.
Example
From ABAP_RELEASE 7.40 SP02, exception for:
SELECT *
FROM (`SPFLI .`)
INTO TABLE itab
WHERE (`. CARRID = 'LH'`).
Before ABAP_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 ABAP_RELEASE 7.40 SP02, this raises an exception of the class CX_SY_DYNAMIC_OSQL_SEMANTICS.
Example
From ABAP_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 ABAP_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 result set were counted. From ABAP_RELEASE 7.40 SP02, the omission of DISTINCT raises an exception of the class CX_SY_DYNAMIC_OSQL_SYNTAX.
Example
From ABAP_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 comparison operator in a dynamic WHERE condition (which is not possible in the static case). With ABAP_RELEASE 7.40 SP02 and higher, this raises an exception of class CX_SY_DYNAMIC_OSQL_SYNTAX.
Example
From ABAP_RELEASE 7.40 SP02, exception for:
SELECT SINGLE *
FROM spfli
INTO wa
WHERE (`carrid NOT = 'LH'`).
Before ABAP_RELEASE 7.40, SP02 it was possible to use (incorrectly) a dynamic FROM clause combined with the addition ORDER BY PRIMARY KEY to access DDIC projection views containing the same number of key fields and view fields, which is not possible in static cases. From ABAP_RELEASE 7.40, SP02, this raises the exception CX_SY_DYNAMIC_OSQL_SYNTAX.
Example
From ABAP_RELEASE 7.40, SP02, an exception is raised when projection_view has the same number of key fields and view fields.
DATA itab TYPE TABLE OF projection_view.

SELECT *
FROM ('KELLERH_VIEW')
INTO TABLE itab
ORDER BY PRIMARY KEY.
BEGIN_SECTION SAP_INTERNAL_HINT
For DDIC database views the exception is raised only in the strict modes from 7.40, SP05 on.
END_SECTION SAP_INTERNAL_HINT
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 ABAP_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 ABAP_RELEASE 7.40 SP02, the target field must be numeric and a loss of values always produces an exception.
Example
From ABAP_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 expressions 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 alias name and no GROUP BY clause was specified. Before ABAP_RELEASE 7.40 SP02, the short form using count( * ), specified together with an alias name or a GROUP BY clause, produced a runtime error. From ABAP_RELEASE 7.40 SP02, this also produces a syntax error if known statically.
Example
From ABAP_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 built-in types LCHR and LRAW from ABAP Dictionary.
Columns of the types LCHR and LRAW cannot be used in relational expressions of the SQL conditions. Before ABAP_RELEASE 7.40 SP02, this produced a runtime error. From ABAP_RELEASE 7.40 SP02, this also produces a syntax error if known statically.
Example
From ABAP_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 ABAP_RELEASE 7.40 SP02, this produced a runtime error. From ABAP_RELEASE 7.40 SP02, this also produces a syntax error if known statically.
Example
From ABAP_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 ABAP_RELEASE 7.40 SP02, columns of this type read without length fields produced a syntax warning. From ABAP_RELEASE 7.40 SP02, this situation always produces a runtime error.
Example
From ABAP_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 ABAP_RELEASE 7.40 SP02, the subquery was not checked. From ABAP_RELEASE 7.40 SP02, the comparison must be specified (statically or dynamically) even if a subquery is specified.
Example
From ABAP_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 built-in 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 ABAP_RELEASE 7.40, SP02, a syntax warning occurs in the extended program check. This warning can be hidden by a pragma.
Example
From ABAP_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 ABAP_RELEASE 7.40 SP02, this was not identified correctly for locators, either statically or at runtime. From ABAP_RELEASE 7.40 SP02, this produces a syntax error or raises an exception.
Example
From ABAP_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 ABAP_RELEASE 7.40 SP02, this situation produces a syntax check warning.
Example
From ABAP_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 ABAP_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 ABAP_RELEASE 7.40 SP02, this text produced a syntax warning; from ABAP_RELEASE 7.40 SP02, it produces a syntax error.
Example
From ABAP_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 ABAP_RELEASE 7.40 SP02, a syntax warning is produced in this case if known statically; at runtime, an exception is always raised.
Example
From ABAP_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 alias name for an aggregation function must also be specified after SELECT and after GROUP BY. Before ABAP_RELEASE 7.40 SP02, the checks on this situation at runtime were not strict enough and the behavior was platform-dependent. From ABAP_RELEASE 7.40 SP02, a violation of this rule always raises an exception of the class CX_SY_DYNAMIC_OSQL_SEMANTICS .
Example
From ABAP_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 alias name in the SELECT list cannot be the name of a column to which no alias name is assigned. Before ABAP_RELEASE 7.40 SP02, the use of a name of this type after ORDER BY raised an exception. From ABAP_RELEASE 7.40 SP02, this also produces a syntax error if known statically.
Example
From ABAP_RELEASE 7.40 SP02, syntax errors for:
SELECT carrid connid AS carrid
FROM spfli
INTO TABLE itab
ORDER BY carrid.