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.
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.
Cool features. ABAP is getting better with every release.
Interesting!!
Cheers,
Kripa Rangachari.
Great! So useful. ABAP is getting ready for the future.
really cool features in new ABAP release..
Cheers,
Jana
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)
?
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.
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
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:
Well, I keep the new way as a comment …
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.
FOR ALL ENTRIES is not state-of-the-art any more.
In 7.52 you can join internal tables instead.
Thanks :)... I will investigate and update
I have query in which i use Internal table as data source. Is Union supported in such query?