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, Complex CASE

This example demonstrates complex case distinctions in SQL expressions.

Other versions: 7.31 | 7.40 | 7.54

Source Code

    CONSTANTS: both_l  TYPE c LENGTH 20 VALUE 'Both < 50',
               both_gt TYPE c LENGTH 20 VALUE 'Both >= 50',
               others  TYPE c LENGTH 20 VALUE 'Others'.

    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.

    SELECT num1, num2,
           CASE WHEN num1 <  50 AND num2 <  50 THEN @both_l
                WHEN num1 >= 50 AND num2 >= 50 THEN @both_gt
                ELSE @others
           END AS group
           FROM demo_expressions
           ORDER BY group
           INTO TABLE @DATA(results).

    cl_demo_output=>write( results ).

    LOOP AT results ASSIGNING FIELD-SYMBOL(<wa>)
                    GROUP BY ( key = <wa>-group size = GROUP SIZE )
                    INTO DATA(group).
      cl_demo_output=>write( group ).
    ENDLOOP.

    cl_demo_output=>display( ).

Description

In a list of columns specified after SELECT, CASE is used to make a complex case distinction for the content of two columns. The result is a string taken from a host variable. Using the alternative column name defined after AS, the result is assigned to the column with the same name in an internal table declared inline, results. GROUP BY is used to group this table by the results column.