Performing calculations and String Processing

Objectives
After completing this lesson, you will be able to:

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.

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

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code snippet
    
     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'
        ).
    
    Copy code
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.
  5. 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.

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

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code snippet
    
     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'
        ).
    
    Copy code
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.
  5. Place the cursor on one of the numeric functions (for example division and select F1 to open the documentation.
  6. 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

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

  1. Create a new global class that implements interface IF_OO_ADT_CLASSRUN.
  2. Copy the following code snippet to the implementation part of method if_oo_adt_classrun~main( ):
    Code snippet
    
        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'
        ).
    
    Copy code
  3. Select CTRL + F3 to activate the class and F9 to execute it as a console app.
  4. Analyze the console output.
  5. Play around with the expressions and functions in the FIELDS lists of the SELECT statements to get familiar with the syntax and function.

Save progress to your learning plan by logging in or creating an account