Performing Calculations and String Processing in ABAP SQL
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
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
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.
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
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
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
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'
).
Note
