Extracting unique values from an internal table

How to get distinct values from an internal table in ABAP? The answer is: it depends. Over the last few years ABAP has evolved and expressions and statements were added which make this task easier.

Consider below internal table of Sales Order Items.

DATA sales_order_positions TYPE STANDARD TABLE OF vbap.

SELECT * FROM vbap INTO TABLE sales_order_positions UP TO 100 ROWS.

Let’s see how to aggregate Sales Order into table sales_orders_unique.

DATA sales_orders_unique TYPE STANDARD TABLE OF vbap-vbeln.

FOR GROUPS (>= 7.40 SP08)

FOR is an iteration expression and serves as a subexpression for the constructor expressions VALUE. What does that mean? With VALUE a new internal table will be constructed. This internal table is filled with the result of the FOR iteration (“FOR loop”).

In this case, all entries of internal table sales_order_positions are grouped by (GROUP BY) vbeln. The sales order number is returned by value.

sales_orders_unique = VALUE #( 
                                FOR GROUPS value OF <line> IN sales_order_positions 
                                GROUP BY <line>-vbeln WITHOUT MEMBERS 
                                ( value ) 
                              ).

LOOP AT GROUP (>= 7.40 SP08)

This is similar to the usage of the FOR and VALUE expression but should be easier to read if you are not comfortable with the newer ABAP expressions yet. The principal is the same as we know it from SQL statements.

Since access to the groups is not needed WITHOUT MEMBERS is used to increase performance a bit.

LOOP AT sales_order_positions ASSIGNING FIELD-SYMBOL(<vbap>) GROUP BY ( vbeln = <vbap>-vbeln ) WITHOUT MEMBERS ASSIGNING FIELD-SYMBOL(<vbap_unique>).
  INSERT <vbap_unique>-vbeln INTO TABLE sales_orders_unique.
ENDLOOP.

SELECT DISTINGT FROM itab (>= 7.52)

SELECT DISTINCT 
  vbeln
FROM 
  @sales_order_positions AS sales_order_position_itab
INTO TABLE 
  @sales_orders_unique.

Since 7.52 it is also possible to use internal tables as the data source. Depending on the SELECT the data of internal table sales_order_positions will be passed from the application server to the database. Besides an eventual performance issue, this feature is not supported by all database systems. If the database does not support this feature an exception of the class CX_SY_SQL_UNSUPPORTED_FEATURE is raised. SAP HANA supports this feature.

Older Releases

FIELD-SYMBOLS: <itab_line> TYPE vbap.

LOOP AT sales_order_positions ASSIGNING <itab_line>.
  INSERT <itab_line>-vbeln INTO TABLE sales_orders_unique.
ENDLOOP.

SORT sales_orders_unique.
DELETE ADJACENT DUPLICATES FROM sales_orders_unique.

Sorting an internal table followed by a DELETE ADJACENT DUPLICATES statement to remove duplicates. A classic in the ABAP universe and your weapon of choice on older systems. If sales_orders_unique is a HASHED TABLE below alternative is also valid:

DATA: sales_orders_unique TYPE HASHED TABLE OF vbap-vbeln WITH UNIQUE KEY table_line.

LOOP AT sales_order_positions ASSIGNING <itab_line>.
  INSERT <itab_line>-field INTO TABLE sales_orders_unique.
ENDLOOP.

INSERT will set the system variable sy-subrc to 4 in case the entry exists already.

Note: In most cases querying the database to return DISTINCT values will be much faster, especially on HANA systems.