ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Reads →  UNION →  UNION Examples 

SELECT, Union for Building a Ranges Table

This example demonstrates how a ranges table is built using unions.

Other versions: 7.31 | 7.40 | 7.54

Source Code

REPORT demo_union_ranges.

CLASS test_union DEFINITION DEFERRED.

CLASS demo_union DEFINITION FRIENDS test_union.
  PUBLIC SECTION.
    CLASS-METHODS main.
  PRIVATE SECTION.
    CONSTANTS package TYPE tadir-devclass VALUE 'SABAPDEMOS'.
    TYPES prog_ranges TYPE RANGE OF trdir-name.
    CLASS-METHODS get_prog_ranges
      RETURNING VALUE(prog_ranges) TYPE prog_ranges.
ENDCLASS.

CLASS demo_union IMPLEMENTATION.
  METHOD main.
    DATA(prog_ranges) = get_prog_ranges( ).

    cl_demo_output=>write( prog_ranges ).

    SELECT FROM trdir
           FIELDS name
           WHERE name IN @prog_ranges
           ORDER BY name
           INTO TABLE @DATA(programs).

    cl_demo_output=>display( programs ).
  ENDMETHOD.
  METHOD get_prog_ranges.
    SELECT FROM tadir
           FIELDS 'I' AS sign,
                  'EQ' AS option,
                   obj_name AS low,
                   ' ' AS high
           WHERE pgmid    = 'R3TR' AND
                 object   = 'PROG' AND
                 devclass = @package
    UNION
    SELECT FROM tadir
           FIELDS 'I' AS sign,
                  'CP' AS option,
                  concat( rpad( obj_name, 30, '=' ) , '*' ) AS low,
                  ' ' AS high
           WHERE pgmid    = 'R3TR' AND
                 object   = 'CLAS' AND
                 devclass = @package
    UNION
    SELECT FROM tadir
           FIELDS 'I' AS sign,
                  'EQ' AS option,
                  'SAPL' && obj_name AS low,
                  ' ' AS high
           WHERE pgmid    = 'R3TR' AND
                 object   = 'FUGR' AND
                 devclass = @package
    UNION
    SELECT FROM tadir
           FIELDS 'I' AS sign,
                  'CP' AS option,
                  'L' && obj_name && '+++' AS low,
                  ' ' AS high
           WHERE pgmid    = 'R3TR' AND
                 object   = 'FUGR' AND
                 devclass = @package
           ORDER BY low
           INTO TABLE @prog_ranges.
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.
  demo_union=>main( ).

CLASS test_union DEFINITION FOR TESTING RISK LEVEL
                                       HARMLESS DURATION SHORT.
  PRIVATE SECTION.
    METHODS check_ranges FOR TESTING.
ENDCLASS.

CLASS test_union IMPLEMENTATION.
  METHOD check_ranges.
    DATA prog_ranges TYPE RANGE OF trdir-name.

    DATA buffer TYPE TABLE OF tadir-obj_name WITH EMPTY KEY.
    SELECT obj_name
           FROM tadir
           WHERE pgmid    = 'R3TR' AND
                 object   = 'PROG' AND
                 devclass = @demo_union=>package
           INTO TABLE @buffer.

    prog_ranges = VALUE #( FOR <fs> IN buffer
                              ( sign = 'I'
                                option = 'EQ'
                                low = <fs>
                                high = ' ' ) ).

    SELECT obj_name
           FROM tadir
           WHERE pgmid    = 'R3TR' AND
                 object   = 'CLAS' AND
                 devclass = @demo_union=>package
           INTO TABLE @buffer.

    prog_ranges = VALUE #( BASE prog_ranges
                              FOR <fs> IN buffer
                              ( sign = 'I'
                                option = 'CP'
                               low = |{ <fs> WIDTH = 30 PAD = '=' }|
                                     && '*'
                                high = ' ' ) ).

    SELECT obj_name
           FROM tadir
           WHERE pgmid    = 'R3TR' AND
                 object   = 'FUGR' AND
                 devclass = @demo_union=>package
           INTO TABLE @buffer.

    prog_ranges = VALUE #( BASE prog_ranges
                              FOR <fs> IN buffer
                              ( sign = 'I'
                                option = 'EQ'
                               low = 'SAPL' && <fs>
                                high = ' ' ) ).

    SELECT obj_name
           FROM tadir
           WHERE pgmid = 'R3TR' AND
                 object = 'FUGR' AND
                 devclass = @demo_union=>package
           INTO TABLE @buffer.

    prog_ranges = VALUE #( BASE prog_ranges
                              FOR <fs> IN buffer
                              ( sign = 'I'
                                option = 'CP'
                               low = 'L' && <fs> && '+++'
                                high = ' ' ) ).

    SORT prog_ranges BY low.

    cl_abap_unit_assert=>assert_equals(
      EXPORTING
        act                  = prog_ranges
        exp                  = demo_union=>get_prog_ranges( ) ).
  ENDMETHOD.
ENDCLASS.

Description

The aim of this example is to create a list of all ABAP programs in a package. The names of the ABAP programs are in the database table TRDIR and the assignment of repository objects to a package is specified in the database table TADIR. In the case of function groups and class pools from the table TADIR, the names of the associated master programs and include programs must be found in the table TRDIR. This can be done by evaluating the naming conventions. In the example shown here, a ranges table is constructed for use in a WHERE clause. The method get_prog_ranges fills the ranges table in a single ABAP SQL statement using UNION statements. The ranges table is filled in full on the database. In one test class, the test method test_union contains an alternative implementation in which the ranges tables is constructed from the results of four individual SELECT statements in the ABAP program and in which ABAP expressions are used instead of SQL expressions.