Skip to Content

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.

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. 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)

    ?

    (0) 
  2. 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.

    (0) 
  3. 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

    (0) 
    1. Horst Keller 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 …
       

       

       

      (0) 

Leave a Reply