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).
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 @(
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
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
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
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 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.
For more information see Path Expressions.
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 …
Excellent features. Thanks for sharing it with the community.
Thanks for sharing Horst...some nice features here.
Great to see that currently Open SQL is on steroids 🙂
great tip, now the next step, test and see the performance.
I'm always reading "Horst Expressions" here. Don't know why...
You made my day 🙂
I will be using this also. Very funny
Unknown Host ... 😉
Horst Keller, any chance we can get sql_expr in CASE working with date comparisons? i.e. something along the lines of:
SELECT (..) CASE WHEN date_col < @keydate1 THEN 1 ELSE 0 END AS somefield FROM (...).
Not possible yet, but on the todo list.
Great to hear it's in the works!
Add this and subqueries as tables in JOINS and my application servers will just sit there doing nothing, with just about everything pushed down to the DB 😉
Coming soon ....
That would be very nice to be able to calculate with dates in SQL expressions.
The problem is, it must be supported by all DBs in the same way. The solution can be date/time functions that are under development - in the moment for CDS. Some are available with ABAP 7.50 ...
Finally we moved to ABAP 750. So I tried out the new features. But oddly enough I get syntax error on these lines, which I just copied from the examples:
The position is the minus on the second line and it says:
The character “–” is not valid here. A blank may be missing or there may be too many blanks.
As I see no answer to your question, I have tried your code (on a S4HANA 1809 was753). Didn’t work with the error that you describe.
Then I deleted the “–” and replaced it with “-“. Problem solved.
Then I tried it on an older version (ECC,WAS740, sp12). The WHERE-clause doesn’t work there, but the sql expression with subtraction works as expected.
What a tricky trap. The wrong minus character! Great job, Sherlock!
This works for me on NW7.50 SP11.
Jörg is right, you have to use the proper minus sign "-"
We have SAP_ABA 750 (SP16) but unfortunately these interesting 7.54 features (like a += 1) don't seem to work.