ABAP News for Release 7.50 – Host and Other Expressions in Open SQL

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

Host Expressions

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
FROM spfli
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 …

For more information see Host Expressions

SQL Expressions

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:

SELECT carrid, connid, fldate, seatsmax, seatsocc,

       seatsmax – seatsocc AS seatsfree

       FROM sflight

       WHERE seatsmax – seatsocc > @( meth( ) )

       INTO TABLE @DATA(result).

As an example for string functions see the following concatenation of columns into one column with CONCAT:

SELECT CONCAT( CONCAT( carrid,

                       LPAD( carrname,21,’ ‘ ) ),

               LPAD( url,40,’ ‘ ) ) AS line

       FROM scarr

       INTO TABLE @DATA(result).

This concatenation is not possible with the operator && that is available since ABAP 7.40.

For more information see SQL Expressions.

Path Expressions

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:

@AbapCatalog.sqlViewName: ‘DEMO_CDS_USE_ASC’

@AccessControl.authorizationCheck: #NOT_REQUIRED

define view demo_cds_use_assocs

  with parameters p_carrid:s_carrid

  as select from demo_cds_assoc_scarr as scarr

{ scarr.carrname,

  scarr._spfli.connid,

  scarr._spfli._sflight.fldate,

  scarr._spfli._sairport.name }

where scarr.carrid = :p_carrid

The name of the associations are prefixed by an underscore _ and are defined in the following views:

@AbapCatalog.sqlViewName: ‘DEMO_CDS_ASC_CAR’

@AccessControl.authorizationCheck: #NOT_REQUIRED

define view demo_cds_assoc_scarr

  as select from scarr

            association to demo_cds_assoc_spfli as _spfli

              on scarr.carrid = _spfli.carrid

     { _spfli,

       carrid,

       carrname }

@AbapCatalog.sqlViewName: ‘DEMO_CDS_ASC_SPF’

@AccessControl.authorizationCheck: #NOT_REQUIRED

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

     { _sflight,

      _sairport,

       carrid,

       connid,

       airpfrom }

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:

SELECT scarr~carrname,

       \_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.

For more information see Path Expressions.

More News

That’s not all about Open SQL in ABAP 7.50. In an upcoming blog I will show you an enhancement to SELECT that became possible because the INTO clause can and should be placed at its end …