Skip to Content

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.

To report this post you need to login first.

3 Comments

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

  1. Enno Wulff

    Thanks for explaining UNION! I didn’t know that this command has been released…

     

    Do you have any business related example what it could be used for?

    Only one I can think of is internal and external status which are stored in tables TJ02 and TJ30:

     SELECT tj02~istat AS stat from tj02 
         where nodis @space
      UNION
        SELECT tj30~estat AS stat FROM tj30 
         WHERE tj30~stsma '00000003'
      INTO TABLE @data(status).

     

    Problem: How to format the code with UNION??

    btw: More ABAP like would have been: 

    UNION select 1
    WITH select 2
    WITH select 3…

    Nice: It also works with JOINs (Example code: read status + text):

        SELECT tj02~istat  AS stat,
               tj02t~txt30 AS text
          FROM tj02 INNER JOIN tj02t ON tj02~istat tj02t~istat
         WHERE tj02~nodis  @space
           AND tj02t~spras @sy-langu
      UNION
        SELECT tj30~estat  AS stat,
               tj30t~txt30 AS text
          FROM tj30 INNER JOIN tj30t ON tj30~estat tj30t~estat
                                    AND tj30~stsma tj30t~stsma
         WHERE tj30~stsma  '00000003'
           AND tj30t~spras @sy-langu
      INTO TABLE @data(status).

    In this example I do not see a big advantage over separate selects using APPENDING TABLE.

    So UNION IMHO only helps if there are overlapping data in different tables.

    Again: Any ideas where you could need this in SAP related context?

    Some links to UNION

    Horst Kellers Blog:
    https://blogs.sap.com/2015/11/09/abap-news-for-release-750-select-union/

    ABAP-Documentation:
    https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/index.htm?file=abapunion_clause.htm

     

    (1) 
  2. Jelena Perfiljeva

    Wouldn’t “APPENDING CORRESPONDING FIELDS” fail in the first example? The field names are different in Z1 and Z2. CORRESPONDING doesn’t seem to be necessary there at all…

    Not sure about UNION as we’re not there yet release-wise but I agree with Enno that simply adding data together seems to have little advantage over two SELECTs.

    (0) 

Leave a Reply