ABAP 7.50 new features –open host expressions and other expressions in SQL

After a long period of stagnation, the development of Open SQL finally awoke from slumber. Starting with ABAP 7.40, SAP has advanced some key changes to include as much of the features in SQL92 as possible and provide the same functionality as the select in the DDL CDs for open SQL. To achieve these goals, the ABAP Runtime Environment introduces a new SQL parser as the new foundation for Open SQL. As a result, Open SQL can now play some and different roles in ABAP.

While Open SQL is more of a part of the ABAP language itself before 7.40, the SQL keyword becomes more and more prepositional at the same time. One major manifestation of this is the new rules on host variables. Before 7.40, you could use them in open SQL as you would with ABAP variables in other ABAP statements. In fact, this freedom prevents more efficient development. The Open SQL statement is not run in the database until it is converted to native SQL. In order to implement something more complex in a where condition than a simple comparison , Open SQL parser must be able to clearly distinguish between the object at both ends of the operator and the contents of the database, thereby sending the corresponding content to the database. To complete this task, the ABAP variable in Open SQL becomes the full host variable (host variables). Just like the ABAP variable in the native SQL component (EXEC SQL). You can (and should) precede the ABAP host variable in open SQL with the escape character @. In fact, this is the only way you can use all of the new open SQL features after ABAP version 7.40. Other basic modifications introduced in Open SQL are also intended to make them more adaptable to the future, such as comma-delimited, and the add-in of a SELECT statement after the authentic SQL clause. What does authentic mean not to understand, but the meaning of this sentence should refer to the 7.50 to remove the previous limit to into position)

The first benefits of these methods have been released in ABAP 7.40, including SQL expressions that can be used in different operand locations, and the likelihood of inline declarations. In ABAP 7.50, Open SQL is still evolving, and this article will cover some of the new features (there will be more in the future).

This article link: http://www.cnblogs.com/hhelibeb/p/7135899.html

Original title: ABAP News for Release 7.50–host and other Expressions in Open SQL

Host expression

In most places where the host variable can be placed, the operand location of the SQL expression since the 7.40 version or the workspace where the SQL statement was written, the host expression can now be placed as follows:

... @ (abap_expression) ...

The host expression abap_expression can be any ABAP expression, which can be a constructor expression, a table expression, an arithmetic expression, a string expression, a bit expression, a built-in function, a functional method, or a method that is surrounded by parentheses () and prefixed with @. Host expressions in Open SQL are evaluated from left to right, and their results are passed to the database as host variables. In fact, you can treat a host expression as shorthand for assigning a value to the ABAP help variable through the ABAP expression. The following example shows a table expression that reads a value from the inner table carriers and places it at the right end of the Where condition:

SELECT Carrid, Connid, Cityfrom, Cityto         from Spfli        WHERE carrid =         VALUE spfli-KEY name                                          = name]-Carrid                                          OPTIONAL ))         into TABLE @DATA(Result)

I personally like the following statement:

DATA (rnd) = cl_abap_random_int=>Create (               CONV1  ). INSERT  from TABLE @ (   VALUE # (    for09      = I         = rnd->get_next ()        = Rnd->get_next ())).

This code constructs an inner table filled with random numbers, which is then used in the INSERT statement. This is a cool demo program in the ABAP document.

For more information, see Host expressions

An SQL expression

In ABAP 7.50, the use of SQL expressions has been extended:

• In addition to using them in the select list, you can now use them in the left side of the where, have, on, and case as cast operands. Note that this will handle the contained host variable and the host expression as operands of the SQL expression.
• The following SQL functions are now available for SQL expressions: ROUND, CONCAT, Lpad, LENGTH, REPLACE, right, RTRIM, SUBSTRING. The COALESCE function has a maximum parameter limit of 255 now.

An example of a where left-hand arithmetic expression:

SELECT Carrid, Connid, Fldate, Seatsmax, SEATSOCC,       As  seatsfree       from  sflight       WHERE seatsmax–seatsocc >  Meth())       intoTABLE @DATA(Result).

Examples of concatenation of columns using CONCAT with string functions:

SELECT  CONCATCONCAT(Carrid,                       lpad(carrname,+, ")),               Lpad (URL,       from  scarr       to  TABLE @DATA

The string is already unavailable through the operator && concatenation.

For more information, see SQL expressions.

Path expression

A path expression is a concept that you have learned from CDs. If a CDs view exposes an association with the same or different views, it can be accessed through a path expression.

For example, the following CDs view uses a path expression in its select list:

  @AbapCatalog  .sqlviewname: ' Demo_cds_use _asc '   @AccessControl  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 associated name is prefixed with underscore_ and is defined by the following view:

@AbapCatalog. Sqlviewname: ' Demo_cds_asc_car '@AccessControl. Authorizationcheck: #NOT_REQUIREDdefine ViewDemo_cds_assoc_scarr as Select  fromScarr Association  toDemo_cds_assoc_spfli as_spfli onScarr.carrid=_spfli.carrid {_spfli, Carrid, carrname}@AbapCatalog. Sqlviewname: ' DEMO_CDS_ASC_SPF '@AccessControl. Authorizationcheck: #NOT_REQUIREDdefine ViewDemo_cds_assoc_spfli as Select  fromSpfli Association toSflight as_sflight onSpfli.carrid=_sflight.carrid andSpfli.connid=_sflight.connid Association[1..1]  toSairport as_sairport onSpfli.airpfrom=_sairport.id {_sflight, _sairport, Carrid, Connid, airpfrom}

In ABAP 7.50, when you access the CDs view, the SELECT statement for Open SQL can also use such a path expression in its select list or FROM clause. The following SQL statement implements the same functionality as the CDs view above:

 select   Scarr~carrname,  \_spfli-< Span style= "color: #000000;" >connid as  Connid,  \_spfli\_sflight-as  fldate,  \_spfli\_sairport-as  name  from   Demo_cds_assoc_scarr as  scarr  where
     scarr~carrid = @carrid order   by   Carrname, Connid, fldate  into  table  @data  (Result). 

It doesn't look like a big difference, does it? The only thing is that the dots are replaced by backslashes \ (so the path expression looks like those meshes). When these open SQL is compiled, the path expression is converted to join in the database. This can be observed in the ST05.

For more information, see Path expressions.

Other news

This is not all information about open SQL in ABAP 7.50. In the next article I will show the enhancements made to the SELECT statement so that into can be placed at the end of the select ...

ABAP 7.50 new features –open host expressions and other expressions in SQL