1

How can I retrieve the distinct values from an internal table?

I am using the SORT and DELETE ADJACENT DUPLICATES to get what I need, but I would like to improve these kind of selections.

The point is: imagine you have an internal table with two purchase orders information, where each one has two items. How can I get the distinct purchase orders number?

For instance: I've selected the following information from EKPO:

ebeln      | ebelp 
---------- | ----- 
1234567890 | 00010
1234567890 | 00020
1234567891 | 00010
1234567891 | 00020

To get distinct ebeln values:

ebeln     
----------
1234567890
1234567891

For that, I need to sort the table and apply the DELETE ADJACENT DUPLICATES. I would like to know if there is any trick to replace these commands.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
luiserta
  • 43
  • 1
  • 2
  • 8
  • I m afraid, I even do not know any better option, even wit SP8 new table comprehensions. Another way would be to loop over first internal table, read a second one by a special field, if sy-subrc <> 0, then add the looped line from first table into the second table, which will carry no doublettes afterwards. Your way is still better. – icbytes Jul 20 '16 at 10:28
  • The question is, why do you get duplicates in your table in the first place? Maybe there is some space for improvement. – Jagger Jul 20 '16 at 10:30
  • Because the OP already used the word "dinstinct" in the topic, but apparently not in the select statement :-) – icbytes Jul 20 '16 at 10:31
  • 2
    @icbytes That could be the reason but not necessarily. The entries in the internal table could be also a result of some kind of an algorithm. But if the duplicates are indeed a result of a database query then one should consider using `DISTINCT` in such database query in the first place. – Jagger Jul 20 '16 at 10:33
  • 2
    I totally agree, hence the smiley at the end of my comment. – icbytes Jul 20 '16 at 10:35
  • The point is: imagine you have an internal table with two purchase orders information, where each one has two items. How can I get the distinct purchase orders number? – luiserta Jul 20 '16 at 13:36
  • 1
    Please provide your code, because without it, it is hard to tell what can be improved. If you select purchase order by its items then it should be possible to get distinct entries in OpenSQL. – Jagger Jul 20 '16 at 19:07
  • You specifically mention using `FOR`. Is there any particular reason, or is that just an idea? – gkubed Jul 20 '16 at 19:53
  • For instance: I've selected the following information from EKPO: ebeln | ebelp ------ | ------ 1234567890 | 00010 1234567890 | 00020 1234567891 | 00010 1234567891 | 00020 To get distinct ebeln, i need to sort the table and apply the Delete adjacent duplicates. I would like to know if there are any trick to replace these commands – luiserta Jul 22 '16 at 16:16
  • Does this answer your question? [Finding duplicates in ABAP internal table via grouping](https://stackoverflow.com/questions/48810878/finding-duplicates-in-abap-internal-table-via-grouping) – Sandra Rossi Feb 19 '20 at 17:56

3 Answers3

1

COLLECT also results distinct values 

DATA: lt_collect like table of lt_source-some_field.
LOOP AT lt_source INTO ls_source. 
  COLLECT ls_source-some_field INTO lt_collect. 
ENDLOOP.
* lt_collect has distinct values of lt_source-some_field
Sel
  • 1,934
  • 1
  • 22
  • 13
0

To get distinct EBELN what you need to do is simply

SELECT DISTINCT ebeln
  FROM ekpo
  INTO TABLE lt_distinct_ebeln
  WHERE (your_where_condition).

That's all it takes.

Jagger
  • 10,350
  • 9
  • 51
  • 93
  • Yes, that is correct if I do not have any information stored in the internal table. Sometimes during data processing need to select data regarding more than one purchase order. Nevertheless in some point you may need to process specific information to different purchase orders. My question points to that. – luiserta Jul 25 '16 at 17:20
  • Then use two different SQL queries one for distinct purchase orders, second for the items. I bet it will be much faster than reinventing the wheel using internal tables. – Jagger Jul 25 '16 at 18:19
0

An option would be to create a loop and select when the values change. For this to work as you mention, the table must be sorted by the field you are looking for.

loop at GT_TABLE into WA_TABLE.
  on change FIELD.
    *Operation
  endon.
endloop.

Another option is to use the same but with a AT. In order for AT to work, the values from the field select in AT declaration to the left of the table must be the same.

loop at GT_TABLE into WA_TABLE.
  at new WA_TABLE-FIELD.
    *Operation
  endat.
endloop.
Joel Orona
  • 46
  • 5