SORT and DELETE ADJACENT

[Former Member]

hi,

why do we need used the SORT and DELETE ADJACENT if there is a warning msg stated

"Before For ALL Entries internal tbl remove duplicate values" and what are the cause of the issue if we dun not put it ?

Thanks

[former_member209120]

Hi infoset,

See this links

http://help.sap.com/abapdocu_70/en/ABENWHERE_LOGEXP_ITAB.htm

http://help.sap.com/abapdocu_702/en/abenwhere_logexp_itab.htm

With duplicated rows in the resulting set, the addition FOR ALL ENTRIES has the same effect as when the addition DISTINCT is specified in the definition of the selection set. Unlike DISTINCT, the rows are not always deleted from the database system but instead are sometimes first deleted from the result set on the application server. The duplicate rows are then removed from the database system if the SELECT statement can be passed to the database system as a single SQL statement. If the SELECT statement has to be distributed to multiple SQL statements, the aggregation takes place on the application server.

[FredericGirod]

Hi,

FOR ALL ENTRIES is like a WHERE field = table-value1 OR field = table-value2 or field = ....

if you ask several times the same question, you will not have a different answer, but you will take more time to have it.

Make an SQL trace on a FOR ALL ENTRIES (ST05), you will see that SAP make set of WHERE conditions with the content of your table ...

regards

Fred

[philipdavy]

We have to use the 'SORT' statement  before DELETE ADJACENT. Because as the statement DELETE ADJACENT says it is deleting adjacent rows comparing a value. So if it sorted it happens to be "ADJACENT" and delete corresponding row.

Regards,

Philip.

[former_member196098]

Hi

Delete adjacent is a useful command but you need to use it correctly

as you need to know what to compare as well

comparing all fields or comparing certain columns and removing them.

Sort is used as you can see that it will only check the next record

it cant compare all the records.

Code sippet:

Data: lt_spfli type standard table of spfli.

FIELD-SYMBOLS: <lfs_spfli> like LINE OF lt_spfli.

***Test the code here

append initial line to lt_spfli assigning <lfs_spfli>.

<lfs_spfli>-carrid = 'THY'.

<lfs_spfli>-connid = 1111.

append initial line to lt_spfli assigning <lfs_spfli>.

<lfs_spfli>-carrid = 'SGP'.

<lfs_spfli>-connid = 3333.

append initial line to lt_spfli assigning <lfs_spfli>.

<lfs_spfli>-carrid = 'LFH'.

<lfs_spfli>-connid = 1111.

append initial line to lt_spfli assigning <lfs_spfli>.

<lfs_spfli>-carrid = 'THY'.

<lfs_spfli>-connid = 4444.

break sdogan.

*doesnt work !!

delete ADJACENT DUPLICATES FROM lt_spfli.

*now try

sort lt_spfli by carrid ASCENDING.

delete ADJACENT DUPLICATES FROM lt_spfli COMPARING carrid.

[Former Member]

Hi Infoset,

We need SORT and DELETE ADJACENT to delete the duplicate entries.

Ex: suppose records are with key value: 1, 3, 2, 4, 1, 6, 5, 2, 1

Then, Before using DELETE ADJACENT use SORT to sort the records.

like: 1, 1, 1, 2, 2, 3, 4, 5, 6

Then After using DELETE ADJACENT records will be like:

1, 2, 3, 4, 5, 6

This might help you.

Thanks

Dileep Kumar

[former_member184569]

Sorting and deleting adjacent entries before select all entries statement will help in improving the performance of the select statement.

Also, if there are any duplicate entries in source table, it results in duplicate efforts to fetch the data from database.  You can verify this by doing SQL trace ST05 on that query.

When you use for all entries, the system basically splits the query in multiple queries and fetches data and then deletes any duplicates from the resultant table if any.

Check this link which explains the process, the issue and solution.

http://zevolving.com/2012/05/performance-of-using-keys-in-select-with-for-all-entries/

[Former Member]

Hi,

     Yes! you can use for all entries without using sort and delete adj... It will work but, perfomance will die.

Just for your understanding.

      In my report i took EBELN from MSEG against multiple MBLNR. now i need PO details.If i use for all entries as it is then system will try to fetch records against each EBELN, either EBELN exist single time or multiple. So first i will delete duplicate records comparing EBELN, and deleting duplicate entries table should be sorted by EBELN. then you will get single EBELN instead of multiple. and then system will fetch PO record without any complication. I am happy to explane you hope my little knowledge will help you.

Regards

Amit