ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Open SQL →  Open SQL - Read Accesses →  SELECT →  SELECT - result →  SELECT - select_list →  SELECT - col_spec →  SELECT - sql_exp →  Examples of SQL Expressions 

SQL Expressions, Arithmetic Calculations

This example demonstrates arithmetic calculations in SQL expressions.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    DATA wa TYPE demo_expressions.

    DELETE FROM demo_expressions.
    DATA(rnd) = cl_abap_random_int=>create(
      seed = CONV i( sy-uzeit ) min = 1 max = 100 ).
    DO 10 TIMES.
      wa-id = |{ sy-index - 1 }|.
      wa-num1 = rnd->get_next( ).
      wa-num2 = rnd->get_next( ).
      INSERT demo_expressions FROM @wa.
    ENDDO.

    DATA(offset) = 10000.
    SELECT id, num1, num2,
           cast( num1 AS fltp ) / cast( num2 AS fltp ) AS ratio,
           div( num1, num2 ) AS div,
           mod( num1, num2 ) AS mod,
           @offset + abs( num1 - num2 ) AS sum
           FROM demo_expressions
           ORDER BY SUM DESCENDING
           INTO TABLE @DATA(results).

    cl_demo_output=>display( results ).

Description

Calculations are made and functions called in a list of columns specified after SELECT.

• To perform floating point division with integer columns, these columns are updated to the type FLTP using cast.

• Integer division with the functions div and mod do not require this update.

• A host variable is added to the absolute value of a difference between column values.

Using the alternative column names defined after AS, the results are assigned to the columns with the same names of an internal table declared inline, results. The result of the final calculation are used as a sort criterion.