Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Performing Calculations and String Processing in ABAP SQL

Objectives

After completing this lesson, you will be able to:

Perform calculations on the database.
Perform string processing on the database.

Arithmetic Expressions

In ABAP SQL, arithmetic expressions can be used as elements of the field list.

ABAP SQL supports the four basic operators, brackets and three different arithmetics (integer, decimal, and floating point). The value range and precision of intermediate results highly depends on the arithmetic that is used. The arithmetic is derived from the operands used in the expression.

The arithmetic is derived from the operands used in the expression:

If the expression contains only integer operands, the expression is an integer expression and the result is of integer type, too.
As soon as the expression contains an operand of decimal type (Dictionary types DEC, QUAN, CURR, ABAP type p), the expression result is of type DEC.
If all operands have a binary floating point type (Dictionary type FLTP, ABAP type f), the expression is a floating point expression and the result is of type FLTP.

The following important restrictions exist:

The division operator (/) is only allowed in floating point expressions.
It is not possible to use floating point types and other numeric types in the same expression.

Hint

You can use any numeric operand in a floating point expression by converting the type to FLTP using CAST( ).

Example: Some Arithmetic Expressions in ABAP SQL

This example shows two examples for arithmetic expressions in the filed list of an ABAP SQL SELECT statement.

Field SEATS_AVAILABLE is calculated by subtracting the number of occupied seats (table field SEATS_OCCUPIED) from the overall number of seats on that flight (table field SEATS_MAX). Because both operands are of type integer, the result of this expression is also an integer.

Field PERCENTAGE_FLTP calculates the percentage of occupied seats as a number between 0 and 100. For this, it is necessary to multiply the number of occupied seats with literal 100 and divide the result by the overall number of seats. Because the division operator is only available in floating point expressions, all operands have to be converted into type FLTP. As a consequence, the result is also of type FLTP, which is displayed in scientific notation by default.

Note

The number literal 100 has type integer and therefore has to be converted to type FLTP, too. Alternatively, you could define a constant of type f with value 100.0 and use this constant in the SQL expression.

Try It Out: Arithmetic Expressions

Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
Code Snippet
Copy code
Switch to dark mode
```
1234567891011121314151617181920

 SELECT FROM /dmo/flight
         FIELDS seats_max,
                seats_occupied,

                seats_max - seats_occupied           AS seats_avaliable,

                (   CAST( seats_occupied AS FLTP )
                  * CAST( 100 AS FLTP )
                ) / CAST(  seats_max AS FLTP )       AS percentage_fltp

           WHERE carrier_id = 'LH' AND connection_id = '0400'
            INTO TABLE @DATA(result).

    out->write(
      EXPORTING
        data   = result
        name   = 'RESULT'
    ).
```
Select CTRL + F3 to activate the class and F9 to execute it as a console app.
Analyze the console output.
Try your own arithmetic expressions to get familiar with the syntax.

Numeric Functions

ABAP SQL offers various built-in functions to supplement calculations with arithmetic expressions. A full list can be found in the ABAP language documentation.

Some important numeric SQL Functions are as follows:

Function DIV( ) performs an integer division (without remainder) and only allows integer values as input.
Function MOD( ) returns the integer remainder of such a division.
Function DIVISION( ) allows any numeric input except for floating point values. The precision of the decimal result is specified by the third parameter of the function.
Function ROUND( ) allows you to explicitly round a numeric value to a given position.

Example: Use of Numeric Functions in ABAP SQL

The example illustrates the use of numeric functions in ABAP SQL. Function DIV( ) allows you to calculate the percentage value as an integer whereas function DIVISION( ) returns the result as a decimal value.

Note that DIV( ) cuts of the remainder so that 48.78787… becomes 48. DIVISION( ), on the other hand, uses commercial rounding to round to the specified number of decimals so that 48.787878… becomes 48.79 instead of 48.78.

Try It Out: Numeric Functions

Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
Code Snippet
Copy code
Switch to dark mode
```
1234567891011121314151617181920212223

 SELECT FROM /dmo/flight
         FIELDS seats_max,
                seats_occupied,

                (   CAST( seats_occupied AS FLTP )
                  * CAST( 100 AS FLTP )
                ) / CAST(  seats_max AS FLTP )                  AS percentage_fltp,

                div( seats_occupied * 100 , seats_max )         AS percentage_int,

                division(  seats_occupied * 100, seats_max, 2 ) AS percentage_dec

          WHERE carrier_id    = 'LH'
            AND connection_id = '0400'
           INTO TABLE @DATA(result).

    out->write(
      EXPORTING
        data   = result
        name   = 'RESULT'
    ).
```
Select CTRL + F3 to activate the class and F9 to execute it as a console app.
Analyze the console output.
Place the cursor on one of the numeric functions (for example division and select F1 to open the documentation.
Look for other numeric functions in the documentation and try them out to get familiar with their possibilities.

String Processing

In ABAP, operator && allows you to combine char-like arguments into one long string. You can use this operator also in ABAP SQL to perform the concatenation operations already on the database. In the first example, the title, first name, and last name of a customer are combined to the full name. In the second example, street, postal code, and city name are combined to the address of the customer. As && does not add separators of any kind, the blanks have to be added manually.

String Processing Functions

ABAP SQL also offers a lot of built-in functions for string processing. A full list can be found in the ABAP language documentation.

Some important string functions in ABAP SQL are as follows:

Function CONCAT( ) concatenates two strings (without blanks).
Function CONCAT_WITH_SPACE( ) does the same but inserts a specified number of blanks in the middle.
Functions UPPER( ) and LOWER( ) transforms an argument to uppercase / lowercase.
Function INITCAP( ) works like LOWER( ) but transforms the first letter of each word to upper case.
Functions LEFT( ) extracts the first n characters from a given argument. RIGHT( ) does the same but starts from the right.
Function SUBSTRING( ) extracts a given number of characters, starting from a given position.

The example constructs the address of a customer by concatenating street, postal_code, and city. It does so in two different ways: once using the concatenation operator && and once using nested sql functions.

Note

Operator && ignores trailing blanks. It only keeps a blank in literal ' '.

This example illustrates the effect of functions UPPER( ), LOWER( ), and INITCAP( ). Note how differently the functions transform 'GmbH' in the carrier name.

This example illustrates the use of string processing functions to extract the year, the month, and the day information from a date field.

Note

Keep in mind that the internal format for date fields is YYYYMMDD. The hyphens in the first column belong to the output format for date fields (ISO-format). They are not present in the internal format. The internal becomes visible after casting the date field to type CHAR( 8 ). This you can see in the second column.

Try It Out: String Processing

Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
Code Snippet
Copy code
Switch to dark mode
```
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465

    SELECT FROM /dmo/customer
         FIELDS customer_id,

                street && ',' && ' ' && postal_code && ' ' && city   AS address_expr,

                concat( street,
                        concat_with_space(  ',',
                                             concat_with_space( postal_code,
                                                                upper(  city ),
                                                                1
                                                              ),
                                            1
                                         )
                     ) AS address_func

          WHERE country_code = 'ES'
           INTO TABLE @DATA(result_concat).

    out->write(
      EXPORTING
        data   = result_concat
        name   = 'RESULT_CONCAT'
    ).

**********************************************************************

    SELECT FROM /dmo/carrier
         FIELDS carrier_id,
                name,
                upper( name )   AS name_upper,
                lower( name )   AS name_lower,
                initcap( name ) AS name_initcap

         WHERE carrier_id = 'SR'
          INTO TABLE @DATA(result_transform).

    out->write(
      EXPORTING
        data   = result_transform
        name   = 'RESULT_TRANSLATE'
    ).

**********************************************************************

  SELECT FROM /dmo/flight
       FIELDS flight_date,
              cast( flight_date as char( 8 ) )  AS flight_date_raw,

              left( flight_Date, 4   )          AS year,

              right(  flight_date, 2 )          AS day,

              substring(  flight_date, 5, 2 )   AS month

        WHERE carrier_id = 'LH'
          AND connection_id = '0400'
         INTO TABLE @DATA(result_substring).

    out->write(
      EXPORTING
        data   = result_substring
        name   = 'RESULT_SUBSTRING'
    ).
```
Select CTRL + F3 to activate the class and F9 to execute it as a console app.
Analyze the console output.
Play around with the expressions and functions in the FIELDS lists of the SELECT statements to get familiar with the syntax and function.

Perform Data Processing on the Database

In this exercise, you push the calculation of free seats and the concatenation of airline ID and airline name down to the database.

Template:

/LRN/CL_S4D401_DBS_NESTED_JOIN (Global Class)

Solution:

/LRN/CL_S4D401_DBS_PUSH_DOWN (Global Class)

Task 1: Copy Template (Optional)

Copy the template class. If you finished the previous exercise, you can skip this task and continue editing your class ZCL_##_SOLUTION.

Steps

Copy class /LRN/CL_S4D401_DBS_NESTED_JOIN to a class in your own package (suggested name: ZCL_##_SOLUTION, where ## stands for your group number).
1. In the Project Explorer view, right-click class /LRN/CL_S4D401_DBS_NESTED_JOIN to open the context menu.
2. From the context menu, choose Duplicate ....
3. Enter the name of your package in the Package field. In the Name field, enter the name ZCL_##_SOLUTION, where ## stands for your group number.
4. Adjust the description and choose Next.
5. Confirm the transport request and choose Finish.
Activate the copy.
1. Press Ctrl + F3 to activate the class.

Task 2: Push Down Calculation

In local class LCL_PASSENGER_FLIGHT, extend the row type of static attribute flight_buffer with a component seats_free. Adjust the implementation of method GET_FLIGHTS_BY_CARRIER and add the calculation of free seats to the SELECT statement. Then adjust the implementation of the CONSTRUCTOR method. Add the calculation to the SELECT SINGLE statement and use the calculation result to fill instance attribute seats_free.

Steps

In local class LCL_PASSENGER_FLIGHT, navigate to the definition of structure type ST_FLIGHTS_BUFFER.
1. For example, you can open the Local Types tab in the editor and search for BEGIN OF st_flights_buffer.
2. Alternatively, you can expand ZCL_##_SOLUTION → LCL_PASSENGER_FLIGHT in the Outline view on the left and choose ST_FLIGHTS_BUFFER.
After component seats_occupied, add a new component seats_free of the same type as type as instance attribute seats_free.
1. Adjust the code as follows:
  Code Snippet
  Copy code
  Switch to dark mode
```
123456789101112

  TYPES: BEGIN OF st_flights_buffer,
           carrier_id     TYPE /lrn/passflight-carrier_id,
           connection_id  TYPE /lrn/passflight-connection_id,
           flight_date    TYPE /lrn/passflight-flight_date,
           plane_type_id  TYPE /lrn/passflight-plane_type_id,
           seats_max      TYPE /lrn/passflight-seats_max,
           seats_occupied TYPE /lrn/passflight-seats_occupied,
           price          TYPE /lrn/passflight-price,
           currency_code  TYPE /lrn/passflight-currency_code,
         END OF st_flights_buffer.   
```
Navigate to the implementation of method GET_FLIGHTS_BY_CARRIER and add the calculation of the free seats to the field list of the SELECT statement.
Caution
Make sure the calculation is at the same position as the new component of structure type ST_FLIGHTS_BUFFER. Alternatively, you can use addition CORRESPONDING FIELDS OF in the INTO clause.
1. Adjust the code as follows:
  Code Snippet
  Copy code
  Switch to dark mode
```
123456789

  SELECT
    FROM /lrn/passflight
  FIELDS carrier_id, connection_id, flight_date,
         plane_type_id, seats_max, seats_occupied,
         price, currency_code
   WHERE carrier_id = @i_carrier_id
    INTO TABLE @flights_buffer.
```
Navigate to the implementation of method CONSTRUCTOR and add the calculation of the free seats to the field list of the SELECT SINGLE statement.
1. Adjust the code as follows:
  Code Snippet
  Copy code
  Switch to dark mode
```
1234567891011

  SELECT SINGLE
          FROM /lrn/passflight
        FIELDS plane_type_id,
               seats_max, seats_occupied,
               price, currency_code
         WHERE carrier_id    = @i_carrier_id
           AND connection_id = @i_connection_id
           AND flight_date   = @i_flight_date
          INTO CORRESPONDING FIELDS OF @flight_raw .
```
Scroll down to the value assignment to instance attribute seats_free. Replace the arithmetic expression on the right-hand side of the assignment operator with the content of flight_raw-seats_free.
1. Adjust the code as follows:
  Code Snippet
  Copy code
  Switch to dark mode
```
1234

  planetype = flight_raw-plane_type_id.
  seats_max = flight_raw-seats_max.
  seats_occ = flight_raw-seats_occupied.
```

Task 3: Push Down String Concatenation

In local class LCL_CARRIER, adjust the implementation of the CONSTRUCTOR method. Move the concatenation of the airline ID and the airline name to the field list of the SELECT SINGLE statement and store the result in instance attribute name.

Steps

In local class LCL_CARRIER, navigate to the implementation of the CONSTRUCTOR method.
1. Perform this step as before.
Locate the value assignment for the name attribute. Analyze it, then add a comment sign at the beginning of the code row.
1. Scroll down to statement name = carrier_id && ` ` && name.
2. Place the cursor anywhere in this code row and press Ctrl + <.
In the field list of the SELECT SINGLE statement, replace database table field NAME with a call of a suitable string processing function to concatenate the ID and the name of the airline separated by a single space.
Note
Alternatively, you can copy the string expression carrier_id && ` ` && carrier_name to the field list. However, be aware that string literals (with back quotes) are not allowed in ABAP SQL.
1. Adjust the code as follows:
  Code Snippet
  Copy code
  Switch to dark mode
```
123456

 SELECT SINGLE
      FROM /lrn/carrier
     WHERE carrier_id = @i_carrier_id
     INTO ( @me->name, @me->currency_code ).
```
Activate and test your global class as console app.
1. Press Ctrl + F3.
2. Press F9.

Next lesson