ABAP News for Release 7.50 - SELECT UNION

[horst_keller]

I promised to tell you, why the INTO clause should be placed behind all the other clauses in an Open SQL SELECT statement. One reason is that Open SQL also wanted to support the SQL syntax addition UNION. An UNION addition can be placed between SELECT statements in order to create the union of the result sets. ABAP CDS offered its UNION from the beginning (7.40, SP05).  If you wanted to use it in Open SQL, you had to wrap it in a CDS view. What hindered Open SQL? Well, the position of the ABAP specific INTO clause before the WHERE, GROUP BY and ORDER BY clauses. These clauses can be part of any SELECT statement participating in unions and there must be only one INTO clause at the end. Therefore, with 7.40, SP08, as a first step, the INTO clause was given a new position.

Now, with ABAP 7.50, we can bring the harvest in. Let me show you an example. The task is to get the names of all the ABAP source texts of a package. These might be needed for searching in the sources or for dumping them in a file. All programs can be found in the database table TRDIR. You know, that the source code files of some ABAP program types like class pools and function pools are distributed over include programs. In order to select the correct technical names of the include programs, it is not a bad idea to construct a ranges table that does the search for you based on some known features.

Before ABAP 7.50, the construction of such a ranges table might have looked as follows:

DATA prog_range TYPE RANGE OF trdir-name.



SELECT 'I' AS sign, 'EQ' AS option, obj_name AS low, ' ' AS high

        FROM tadir

        WHERE pgmid = 'R3TR' AND object = 'PROG' AND devclass = @devclass

        INTO TABLE @prog_range.



SELECT 'I' AS sign, 'CP' AS option, obj_name && '*' AS low, ' ' AS high

        FROM tadir

        WHERE pgmid = 'R3TR' AND object = 'CLAS' AND devclass = @devclass

        APPENDING TABLE @prog_range.

* Not perfect, from 7.51 on the following is better

*SELECT 'I' AS sign, 'CP' AS option, concat( rpad( obj_name, 30, '=' ), '*' ) AS low, 

*        ' ' AS high

*        FROM tadir

*        WHERE pgmid = 'R3TR' AND object = 'CLAS' AND devclass = @devclass

*        APPENDING TABLE @prog_range.



SELECT 'I' AS sign, 'EQ' AS option, 'SAPL' && obj_name AS low, ' ' AS high

        FROM tadir

        WHERE pgmid = 'R3TR' AND object = 'FUGR' AND devclass = @devclass

        APPENDING TABLE @prog_range.



SELECT 'I' AS sign, 'CP' AS option, 'L' && obj_name && '+++' AS low, ' ' AS high

        FROM tadir

        WHERE pgmid = 'R3TR' AND object = 'FUGR' AND devclass = @devclass

        APPENDING TABLE @prog_range.

Four individual SELECT statements are used to fill one internal table prog_range with the help of the APPENDING addition. Note the usage of string expressions in the SELECT lists.

With ABAP 7.50 you can pack the four SELECT statements into one (this can be called code push down):

DATA prog_range TYPE RANGE OF trdir-name.



SELECT 'I' AS sign, 'EQ' AS option, obj_name AS low, ' ' AS high

       FROM tadir

       WHERE pgmid = 'R3TR' AND object = 'PROG' AND devclass = @devclass

UNION

SELECT 'I' AS sign, 'CP' AS option, obj_name && '*' AS low, ' ' AS high

        FROM tadir

        WHERE pgmid = 'R3TR' AND object = 'CLAS' AND devclass = @devclass

*Not perfect, from 7.51 on the following is better

*SELECT 'I' AS sign, 'CP' AS option, concat( rpad( obj_name, 30, '=' ) , '*' ) AS low, 

*       ' ' AS high

*       FROM tadir

*       WHERE pgmid = 'R3TR' AND object = 'CLAS' AND devclass = @devclass

UNION

SELECT 'I' AS sign, 'EQ' AS option, 'SAPL' && obj_name AS low, ' ' AS high

       FROM tadir

       WHERE pgmid = 'R3TR' AND object = 'FUGR' AND devclass = @devclass

UNION

SELECT 'I' AS sign, 'CP' AS option, 'L' && obj_name && '+++' AS low, ' ' AS high

       FROM tadir

       WHERE pgmid = 'R3TR' AND object = 'FUGR' AND devclass = @devclass

       INTO TABLE @prog_range.

The result is the same as above and can be used to get the program names, e.g. as follows:

SELECT name

       FROM trdir

       WHERE name IN @prog_range

       ORDER BY name

       INTO TABLE @DATA(programs).

(The example is not bullet-proof, but well an example and you might extend it...)

As shown here, with UNION you can unite the result sets from SELECT statements for one database table, but it is also possible to combine the result sets of different database tables, if the numbers of columns and the column types match.

For more information and examples see SELECT - UNION.