New ABAP from version 7.4

New Commands in OpenSQL for ABAP 7.4

CASE Statements In OPEN SQL Queries in ABAP 7.4

One of the new features of ABAP 7.4 is the ability to use CASE statements in Open SQL queries. The code below shows an example of this. In this example there is a field in a local structure named ERNAM, and it should be filled with the literals “NAME1″, “NAME2″, or “NAME3″ respectively, depending on the contents of the database field AUART (DocType).

DATA: ls_vbak TYPE vbak,
     ld_vbeln LIKE vbak-vbeln.

PARAMETERS: p_vbeln like vbak-vbeln.

CONSTANTS: lc_name1(5) TYPE c VALUE 'name1',
          lc_name2(5) TYPE c VALUE 'name2',
          lc_name3(5) TYPE c VALUE 'name3'.

ld_vbeln = p_vbeln.

SELECT vbeln, vbtyp,
CASE
  WHEN auart = 'ZAMA' THEN @lc_name1
  WHEN auart = 'ZACR' THEN @lc_name2
 ELSE @lc_name3
END AS ernam
FROM vbak
WHERE vbeln = @ld_vbeln
INTO CORRESPONDING FIELDS of @ls_vbak.
ENDSELECT.

SELECT vbeln, vbtyp,
CASE
  WHEN auart = 'ZAMA' THEN @lc_name1
  WHEN auart = 'ZACR' THEN @lc_name2
 ELSE @lc_name3
END AS ernam
FROM vbak
WHERE vbeln = @ld_vbeln
INTO CORRESPONDING FIELDS of @ls_vbak.
ENDSELECT.

Please make note that you have to put an @ symbol in front of your ABAP variables (or constants) when using new features, such as CASE, in order to let the compiler know that you are not talking about a field in the database. (This is called “Escaping” the Host Variable). You also have to put commas between the fields you are bringing back from the database and put the INTO statement at the end. This is a result of a new “strict” syntax check that comes into force when the compiler notices you are using one of the new features. In this way, SAP can still being backward compatible.

Why did I use this as my first example? Surely you have used the CASE statement on data AFTER you have retrieved it. So what have we gained or even done by placing the CASE inside the SELECT? Well, what the CASE statement has allowed you to do is outsource the conditional logic to the database, as opposed to performing the CASE on the application server.

If you are unfamiliar with coding ABAP on HANA, the paradigm shift of pushing logic down into the SAP HANA database to be processed is new, but can result in huge performance improvements. While this example is not HANA specific, it does introduce you to the “concept of pushing code down” to the database layer. Something in the past we have been told to avoid. The new paradigm in ABAP is “Code-to-Data”. We will learn to create VALUE by optimizing the backend DBMS (HANA).

Perform Calculations within SQL Statements in ABAP 7.4

Another feature that is new to release 7.4 is the ability to perform arithmetic operations inside of SQL statements. Before 7.4 you had to select the data first, then you could perform calculations on it. This is best explained with an example. Let’s say we are selecting against table SFLIGHT. We want all rows for United Airlines connection id 941. For each row, we will add together the total occupied seats in Business Class and First Class, then we will multiply that by price and store the result in field paymentsum of our internal table.

DATA: lt_sflight TYPE TABLE OF sflight.

CONSTANTS: lc_carrid TYPE s_carr_id VALUE 'UA',
           lc_connid TYPE s_conn_id VALUE '941'.
 
SELECT carrid, connid, price, seatsocc_b, seatsocc_f,
   ( ( seatsocc_b + seatsocc_f ) ) * price AS paymentsum
   FROM sflight
    WHERE carrid = @lc_carrid
      AND connid = @lc_connid
     INTO CORRESPONDING FIELDS of TABLE @lt_sflight.

In-Line Declarations within SQL Statements in ABAP 7.4

ABAP 7.4 has removed the need to create the data declaration for an internal table or structure. In prior versions of ABAP, if you declared a TYPE and then suddenly wanted to retrieve an extra field in your SELECT, then you would need to make the change in two places: in the TYPE definition and in the SELECT statement. In ABAP 7.4, however, you can not only skip the TYPE definition but the internal table declaration as well. An example of this is shown below:

SELECT carrname AS name, carrid AS id
      FROM   scarr
      INTO TABLE @DATA(result).

Look at the debugger screen shot below:

As you can see, the table is created at the instant the database is accessed, and the format or ABAP TYPE of the table is taken from the types of the data fields you are retrieving.

This also works for structures if you are doing a SELECT on multiple database fields. The column name can also be influenced in the target internal table using the AS <variable> construct. So in the example below, in the internal table result, CARRNAME will be called NAME and CARRID will be called ID.

SELECT SINGLE carrname AS name, carrid AS id
      FROM   scarr
      WHERE  carrid = @id
      INTO @DATA(result).

INNER Join Column Specification in ABAP 7.4

As developers we (you) probably use Inner Joins frequently. In ABAP 7.4 we can utilize the ASTERISK in much the same way we can use it in a SELECT *. In the SELECT list, you can now specify all columns of a data source using the new syntax data_source~* (see below:)

SELECT scarr~carrname, spfli~*, scarr~url
       FROM scarr INNER JOIN spfli ON scarr~carrid = spfli~carrid
       INTO TABLE @DATA(result).

Take a look at the Debugger screen shot below:

You can see that SPFLI has been added to the table RESULT. Please remember to address the data for SPFLI you would need to code as follows…

RESULT[n]-SPFLI-data_element

Also, please, please, please… be mindful when using the asterisk. It acts just like the wild card in SELECT * and can impact performance if you really didn’t want all of the columns.

Once again, I’d want to mention Paul Hardy’s excellent book ABAP® to the Future. Click the image below to pick up a copy, it will definitively become your go-to reference as the new features of ABAP are rolled out where you work!

Value Operator VALUE

A constructor expression with the value operator VALUE creates a result of a data type specified using type. The following can be specified for type:

• A non-generic data type dtype.

• Exceptions to this rule are:

• When an initial value VALUE #( ) is passed to a generically typed formal parameter, the type is derived from the generic type.

• The operand can be evaluated after BASE when a structure or an internal table is constructed.

• When used for a single table expression VALUE #( table_expo ).

1. Definition

Variables:    VALUE dtype|#( )

Structures:   VALUE dtype|#( comp1 = a1 comp2 = a2 … )

Tables:         VALUE dtype|#(  ( … )  ( … ) … ) …

2. Example for structures

TYPES:  BEGIN OF ty_columns1, “Simple structure

                    cols1 TYPE i,

                    cols2 TYPE i,

                 END OF ty_columns1.

     

TYPES:  BEGIN OF ty_columnns2,  “Nested structure

                    coln1 TYPE i,

                    coln2 TYPE ty_columns1,

                END OF ty_columns2.

data(struct_nest) = VALUE ty_columns2( coln1 = 1

                                                                           coln2-cols1 = 1

                                                                            coln2-cols2 = 2 ).

OR

data(struct_nest)   = VALUE ty_columns2( coln1 = 1

                                                            coln2 = VALUE #( cols1 = 1

                                                                    cols2 = 2 )

                                                ).

Use # if it is not inline declaration

There is always space between open bracket and its elements.

3. Examples for internal tables

Elementary line type:

TYPES t_itab TYPE TABLE OF i WITH EMPTY KEY.

DATA itab TYPE t_itab.

itab = VALUE #( ( ) ( 1 ) ( 2 ) ).

Structured line type (RANGES table):

DATA itab TYPE RANGE OF i.

itab = VALUE #( sign = ‘I’  option = ‘BT’  ( low = 1  high = 10 )

                                                               ( low = 21 high = 30 )

                                                               ( low = 41 high = 50 )

                                         option = ‘GE’ ( low = 61 )  

                        ) .

GROUP BY for Internal Tables

DATA flights TYPE TABLE OF spfli WITH EMPTY KEY.

SELECT * FROM  spfli

     WHERE carrid = '…'

     INTO TABLE @flights.

DATA members LIKE flights.

LOOP AT flights INTO DATA(flight)

    GROUP BY ( carrier = flight-carrid

               cityfr = flight-cityfrom )  //the <group> will have 2 fields                                                   //carrier & cityfr

          ASCENDING

          ASSIGNING FIELD-SYMBOL(<group>).

 CLEAR members.

 LOOP AT GROUP <group> ASSIGNING FIELD-SYMBOL(<flight>).

members = VALUE #( BASE members ( <flight> ) ).

 ENDLOOP.

 cl_demo_output=>write( members ).

ENDLOOP.

cl_demo_output=>display( ).

Looks like dreaded nested LOOPs, but it isn’t quite that – no quadratic behavior!  What happens here is that the first LOOP statement is executed over all internal table lines in one go and the new GROUP BY addition groups the lines. Technically, the lines are bound internally to a group that belongs to a group key that is specified behind GROUP BY. The group key is calculated for each loop pass. And the best is, it need not be as simple as using only column values, but you can use any expressions here that normally depend on the contents of the current line, e.g. comparisons, method calls, …. The LOOP body is not evaluated in this phase!

Only after the grouping phase, the LOOP body is evaluated. Now a second (but not nested) loop is carried out over the groups constructed in the first phase. Inside this group loop you can access the group using the field symbol <group> that is assigned to the group in the above example. If you want to access the members of the group, you can use the new LOOP AT GROUP statement, which enables a member loop within the group loop. In the example, the members are inserted into a member table and displayed.

Here another example, where the group key is evaluated from method calls:

LOOP AT flights INTO DATA(wa)

GROUP BY ( tz_from = get_time_zone( wa-airpfrom )

                        tz_to   = get_time_zone( wa-airpto ) )

ASSIGNING FIELD-SYMBOL(<group>).

 …

ENDLOOP.

Of course, there is also expression enabled syntax for grouping internal tables.

In a first step, we get rid of LOOP AT GROUP by replacing it with a FOR expression:

DATA members LIKE flights.

LOOP AT flights INTO DATA(flight)

GROUP BY ( carrier = flight-carrid

               cityfr = flight-cityfrom )

          ASCENDING

          ASSIGNING FIELD-SYMBOL(<group>).

 members = VALUE #( FOR m IN GROUP <group> ( m ) ).

 cl_demo_output=>write( members ).

ENDLOOP.

cl_demo_output=>display( ).

The IN GROUP is a new addition to FOR. Second, away with the outer LOOP:

TYPES t_flights LIKE flights.

DATA out TYPE REF TO if_demo_output.

out = REDUCE #( INIT o = cl_demo_output=>new( )

               FOR GROUPS <group> OF flight IN flights

            GROUP BY ( carrier = flight-carrid cityfr = flight-cityfrom )

              ASCENDING

            LET members = VALUE t_flights( FOR m IN GROUP <group> ( m ) ) IN

            NEXT o = o->write( members ) ).

out->display( ).

FOR GROUPS is another new FOR variant. Believe me, it does the same as the variants above. But for reasons of readability, the combination of LOOP AT GROUP with a FOR IN GROUP within might be the preferable one, at least for this example.

Using the Chaining Operator in ABAP 7.4

The Chaining Operator && can be used to create one character string out of multiple other strings and literals. The use of the chaining operator largely replaces the CONCATENATE statement. In this example, three variables are concatenated together using the && chaining operator.

DATA: v_var1 TYPE char30,
     v_var2 TYPE char30,
     v_var3 TYPE char30.
DATA: lv_result TYPE string.   
v_var1 = 'Building'.
v_var2 = 'A'.
v_var3 = 'String'.   
lv_result = v_var1 && v_var2 && v_var3.
WRITE: /(30) 'Using &&', lv_result