As many of you already know, ABAP supports the UNION command now. UNION is an SQL command to combine the results of two separate queries into one dataset.

Here is a sample dataset from the imaginary database table Z1:

ID
TEXT
001
One
002
Two

And here is a sample dataset from another imaginary database table Z2:

CODE
NAME
002
Two
016
Sixteen
019
Nineteen

Before the UNION command existed, we had to run two distinct queries to combine this data into a single internal table.

SELECT id AS key, text AS value FROM z1 INTO TABLE @DATA(lt_itab). SELECT code AS key, name AS value FROM z2 APPENDING CORRESPONDING FIELDS OF @lt_itab.

Now, UNION allows us to merge those queries.

SELECT id AS key, text AS value FROM z1 UNION code AS key, name AS value FROM z2 INTO TABLE @DATA(lt_itab).

Here is the catch: If we execute the UNION query above, we get the following resultset:

KEY
VALUE
001
One
002
Two
016
Sixteen
019
Nineteen

Did you notice that record “002” appears only once? In spite of its existence in both of Z1 & Z2, the result set included a singular “002” entry.

That’s the catch with UNION queries. They can be executed with two logical approaches to handle duplicate records.

UNION DISTINCT is the default mode, and it will eliminate duplicate records from the second query. That’s similar to the logic of SELECT DISTINCT or FOR ALL ENTRIES. That’s why “002” from the second table was missing in the resultset.

UNION ALL needs to be specified explicitly, and it tolerates duplicates from the second query. So, let’s modify our sample query accordingly.

SELECT id AS key, text AS value FROM z1 UNION ALL code AS key, name AS value FROM z2 INTO TABLE @DATA(lt_itab).

This time, our recordset will include duplicate records from Z2.

KEY
VALUE
001
One
002
Two
002
Two
016
Sixteen
019
Nineteen

Each approach has its time and place. If I am looking for a unique list of values, such as the combined list of tax numbers of clients & vendors, I would go for UNION DISTINCT. However; if I am querying transactions, such as BSIS / BSAS, I would go for UNION ALL because I probably wouldn’t want to miss any line items.

New NetWeaver Information at SAP.com

Very Helpfull

User Rating: Be the first one !