Skip to Content
Author's profile photo Horst Keller

ABAP News for Release 7.50 – SELECT UNION

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.

Assigned Tags

      13 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Nikolay Evstigneev
      Nikolay Evstigneev

      Some of my mates will be really happy to get UNIONs as they had them before coming to ABAP 🙂

      Thanks! Step by step we're approaching to a better world.

      Author's profile photo Abdul Hakim
      Abdul Hakim

      Cool features. ABAP is getting better with every release.

      Author's profile photo Kripa Rangachari
      Kripa Rangachari

      Interesting!!

      Cheers,

      Kripa Rangachari.

      Author's profile photo Former Member
      Former Member

      Great! So useful. ABAP is getting ready for the future.

      Author's profile photo Janarthanan Elangovan
      Janarthanan Elangovan

      really cool features in new ABAP release..

      Cheers,

      Jana

      Author's profile photo Paul Hardy
      Paul Hardy

      Would this work to get a list of tax numbers for all vendors with either open or closed items?

      SELECT lifnr AS lifnr, stcd1 AS stcd1

        FROM LFA1 UNION DISTINCT

      SELECT lifnr AS lifnr ' ' AS stcd1

        FROM BSIK UNION DISTINCT

      SELECT lifnr AS lifnr ' ' AS stcd1

        FROM  BSAK

      INTO TABLE @DATA(vendor_tax_no_list)

      ?

      Author's profile photo shawn huang
      shawn huang

      Great thanks Horst. One common question is: we often use lower version ABAP kernel e.g. our system's current version of SAP_ABS is 701, how risky is it if we upgrade to 7.5? does SAP provide any guidelines to upgrade the language kernel to speed up the adoption of the new language features, besides all customized programs or user exits, what else impact would you expect if we upgrade it? thanks again.

      Author's profile photo W. Heuvelmans
      W. Heuvelmans

      Hi Horst,

      In your example you talk about the function RPAD but this does not seem to be available?

      I tried LPAD which works but the other way around.

      Regards

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

       

      Oops, yes. In order to improve the code I updated it and by that the 7.51 function RPAD slipped in.

      In 7.50, the following is syntactically correct, but does not find all programs:

       SELECT 'I' AS sign, 'CP' AS option, obj_name && '*' AS low, ' ' AS high
              FROM tadir
              WHERE pgmid = 'R3TR' AND object = 'CLAS' AND devclass = @devclass

      Well, I keep the new way as a comment …
       

       

       

      Author's profile photo Ankit Maskara
      Ankit Maskara

      Hi Horst Keller ,

      I was wondering why the support of For All Entries clause alongwith a Union Clause is not supported together.  I have a use case where I can leverage the same. Right now I have to apply two separate Select statements.

      BR.

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

      FOR ALL ENTRIES is not state-of-the-art any more.

      In 7.52 you can join internal tables instead.

      Author's profile photo Ankit Maskara
      Ankit Maskara

      Thanks :)... I will investigate and update

      Author's profile photo Nimal Nazaruddin
      Nimal Nazaruddin

      I have query in which i use Internal table as data source. Is Union supported in such query?