ABAP News for 7.40, SP08 – Open SQL

The most important news for Open SQL in ABAP 7.40, SP08 are as follows:

Inline Declarations after INTO

You might like this one. From 7.40, SP08 on you can place inline declarations with the declaration operator DATA( … ) that was introduced with 7.40, SP02  after INTO.

DATA id TYPE scarr-carrid.
cl_demo_input=>request( CHANGING field = id ).

SELECT SINGLE carrname AS name, carrid AS id
       FROM   scarr
       WHERE  carrid = @id
       INTO @DATA(result).

cl_demo_output=>display( result ).

Or for a table

SELECT carrname AS name, carrid AS id
       FROM   scarr
       INTO TABLE @DATA(result).

cl_demo_output=>display( result ).

Either an elementary data object, a structure, or an internal table is declared depending on the results set defined in the SELECT list. See the documentation for details of the type construction.

SQL Expressions

The SQL expressions introduced with 7.40, SP05 into the SELECT list were enhanced with 7.40, SP08 as follows:

• You can use SQL expressions after GROUP BY
• You can use SQL expressions together with aggregates
• You can use SQL expressions as argument of aggregates
• You can use a seached CASE expression besides the simple CASE

Example for a searched case:

SELECT num1, num2,

       CASE WHEN col1 <  50 AND col2 <  50 THEN @both_l

            WHEN col1 >= 50 AND col2 >= 50 THEN @both_gt

            ELSE @others

       END AS group

       FROM demo_expressions

       ORDER BY group

       INTO TABLE @DATA(results).

Column Specification

In the SELECT list, you can specify all columns of a data source using the syntax data_source~* from  7.40, SP08 on. This can be handy when working with joins.

SELECT scarr~carrname, spfli~*, scarr~url

       FROM scarr INNER JOIN spfli ON scarr~carrid = spfli~carrid

       INTO TABLE @DATA(result).

Position of INTO

Did you realize the position of INTO in the above examples? I positioned it after the other clauses. This was not possible before. From 7.40, SP08 on, the INTO clause can and should (haha) be used after the other clauses of a SELECT statement. The additions UP TO n ROWS, BYPASSING BUFFER, and CONNECTION that are not treated as clauses must then be placed after the INTO clause.

The rationale behind this change is, that the INTO clause is not part of standard SQL but defines the data interface between SQL and ABAP. In order to enable future enhancements in the SQL part of Open SQL, e.g. UNION, the INTO clause has to be removed from the SQL part.

Removal of Restrictions and New Restrictions

Some restrictions have been removed. E.g. from 7.40, SP08 on you can place a minus sign in front of an operator of an arithmetic expression in the SELECT list, you can carry out a simple CASE for aggregates, you can use LIKE and IN (…) in join conditions after ON, you can specify a subquery after WHERE dynamically.

But if you use any of the new things listed here, as already for SP05, the syntax check for Open SQL is carried out in a strict mode, where stricter syntax rules apply. E.g. you must use comma separated lists and the escape symbol @ in front of host variables. By this, at least in Open SQL ABAP enforces a release dependent  deprecation concept in a downward compatible way. Now what do you think about that?