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
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 (
vbeln. The sales order number is returned by
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
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.
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
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.