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?
