After a long time of stagnation the development of Open SQL awoke from its deep slumber and took some major steps in ABAP 7.40 in order to comprise as many features as possible from SQL92 and to offer about the same functionality as the SELECT statement of the DDL of ABAP CDS. In order to do so, a new foundation for Open SQL was laid by introducing a new SQL parser into the ABAP runtime environment. One consequence of this is the fact that Open SQL plays a bit other role in ABAP than before. While before 7.40 Open SQL was regarded more a part of the ABAP language itself, meanwhile the SQL character becomes more and more pronounced. One of the major indicatons for this is the new role of host variables. Before 7.40, you used ABAP variables in Open SQL statements as it is done in all other ABAP statements. In fact, this prevented further development quiet effectively. Open SQL statements are executed on the database after being transformed to native SQL. In order to push down more sophisticated things than simple comparisons with ABAP variables in WHERE conditions - say SQL expressions in many operand positions - the Open SQL parser must be able to distinguish clearly between operands that are evaluated by the database and ABAP variables whose contents has to be passed to the database. In order to fulfill this task, ABAP variables in Open SQL statements meanwhile fully play the role of host variables as ABAP variables always did in static native SQL (EXEC SQL). You can and should prefix ABAP host variables in Open SQL with @. In fact, you can use all the new SQL features introduced to Open SQL starting with Release 7.40 only if you do so. Other fundamental changes that were introduced to Open SQL in order to make it fit for the future were comma separated lists and placing the INTO addition of a SELECT statement behind the authentic SQL clauses.
As a first benefit of these measures, already with ABAP 7.40 fundamental new features in Open SQL were rolled out, comprising SQL expressions in various operand positions or the possibilty of inline declarations. With ABAP 7.50 this development is continued and this blog introduces some of the new features (more to come).
In almost all positions where you could place host variables, including the operand positions of SQL expressions from 7.40 on or the work areas of writing SQL statements, you can use host expressions now using the syntax
... @( abap_expression ) ...
A host expression abap_expression can be any ABAP expression, that is a constructor expression, a table expression, an arithmetic expression, a string expression, a bit expression, a builtin-function, a functional method, or a method chaining inside parentheses () prefixed with @. The host expressions of an Open SQL statement are evaluated from left to right and their results are passed to the database as it is done for the contents of host variables. In fact you can see host expressions as short cuts for assignments of ABAP expressions to ABAP helper variables and using those as host variables. The following example shows a table expression that reads a value from an internal table carriers on the right hand side of a WHERE condition.
SELECT carrid, connid, cityfrom, cityto
WHERE carrid = @( VALUE spfli-carrid( carriers[ KEY name carrname = name ]-carrid OPTIONAL ) )
INTO TABLE @DATA(result).
I personally like the following:
DATA(rnd) = cl_abap_random_int=>create(
seed = CONV i( sy-uzeit ) min = 1 max = 100 ).
INSERT demo_expressions FROM TABLE @( VALUE #( FOR i = 0 UNTIL i > 9 ( id = i num1 = rnd->get_next( ) num2 = rnd->get_next( ) ) ) ).
An internal table is constructed and filled with random numbers inside an INSERT statement. A cool feature for the ABAP documentation's demo programs ...
With ABAP 7.50, the usage of SQL expressions was extended as follows:
Besides using them in the SELECT list, you can use them as left hand sides of comparisons with WHERE, HAVING, ON, and CASE and as Operands of a CAST expression. Note that this includes host variables and host expressions as operands of SQL expressions.
The following SQL functions can be used in SQL expressions now: ROUND, CONCAT, LPAD, LENGTH, REPLACE, RIGHT, RTRIM, SUBSTRING. The COALESCE function can have up to 255 arguments now.
As an example of an arithmetic expression on the left hand side of a WHERE condition see:
Path expressions are something you know from CDS already (duh!). If a CDS view exposes an association, the same or another view can access it using a path expression.
For example, the following CDS view uses path expressions in its SELECT list:
define view demo_cds_use_assocs
with parameters p_carrid:s_carrid
as select from demo_cds_assoc_scarr as scarr
where scarr.carrid = :p_carrid
The name of the associations are prefixed by an underscore _ and are defined in the following views:
define view demo_cds_assoc_scarr
as select from scarr
association to demo_cds_assoc_spfli as _spfli
on scarr.carrid = _spfli.carrid
define view demo_cds_assoc_spfli
as select from spfli
association to sflight as _sflight
on spfli.carrid = _sflight.carrid and
spfli.connid = _sflight.connid
association [1..1] to sairport as _sairport
on spfli.airpfrom = _sairport.id
With ABAP 7.50 Open SQL's SELECT can also use such path expressions in its SELECT list or FROM clause when accessing CDS views. The following Open SQL statement does the same as the first CDS view above:
\_spfli-connid AS connid,
\_spfli\_sflight-fldate AS fldate,
\_spfli\_sairport-name AS name
FROM demo_cds_assoc_scarr AS scarr
WHERE scarr~carrid = @carrid
ORDER BY carrname, connid, fldate
INTO TABLE @DATA(result).
Looks not too different, eh? Only the dots have to be replaced by backslashes \ (and because of this, the path expressions looks like those for meshes). When compiling such an Open SQL statement, the path expressions are converted to joins on the database. Check it out with ST05.