Select -- for all entries.. fetching Duplicates

[Former Member]

Hi,

Im using a Select- for all entries structure. Actually Select will supress the Duplicates .But i have a table which has 20000 entries , but my select --for all entries fetching around 56000 records..!

Any idea/ anybody have Experience this kind of Problem..?

If i sort the internal table would it resolve the proble..? Because its not happening in all cases.. but for few cases only its extracting duplicates.. other times..its supressing the duplicates.

Appreciate any advise.

[Former Member]

FOR ALL ENTRIES... acutally sorts the resultant internal table and removes the duplicates.

But the point to be noted is the deletion of dulicates is based on the fields in the select clause. so as such no duplicate entries will exist.

eg: select vbeln posnr from vbap into i_vbap for all entries in i_vbak where vbeln = i_vbak-vbeln.

Also to be remembered is that before the FOR ALL ENTRIES statement, try to sort the internal table in the FOR ALL ENTRIES Clause based on the field used in the where clause.

If i am not answering your question, kindly let me know. If you can post the exact SQL query, probably it might help to answer better

Balaji

[Former Member]

Hi,

Ideally for all entries should eliminate the duplicates but how it is happening in your case is strange.

any way before applying the for all entreis

you should sort on the filed which you are using in the wgere clause and apply a delete duplicates statemnt after the sort by comparing the same fields on which you made the sort.

try to consider the full combination of primary key fields.

Regards

Shravan

[christian_wohlfahrt]

Hi!

Sorting and deletion of duplicates in your selection (where-) table is from performance point of view anyway a good idea.

But shouldn't have an influence on the result - nevertheless, if selects are splitted (internal) in several parts, this might occur. But I thought, total result gets deletion of duplicates, not only partial results.

Looks like you have a high redundancy - can you specify more join- or where-clauses?

Regards,

Christian

[Former Member]

Hi

My problem is not that i have duplicates in the resulted internal tables. But in our production one of the table contains 2,00,000 Records , when i use this select .. for all entries.. its fetching 46,00,000 records and gave a dump immeidately after that statement.

I can add anymore fields to the Where clause due to our business process.

Is there anyway( before select or in Select statement ) i can eliminate this Duplicate in the for all entries..?

Is sorting the driver table helpful..?

Message was edited by: Dead Lock

[jayanthi_jayaraman]

Hi,

Try using <b>Distinct</b> in select statement.

Here is the sample code.

TABLES SPFLI.

DATA TARGET LIKE SPFLI-CITYTO.

SELECT DISTINCT CITYTO

INTO TARGET FROM SPFLI

WHERE

CARRID = 'LH ' AND

CITYFROM = 'FRANKFURT'.

WRITE: / TARGET.

ENDSELECT.

Did you tried this ?

[Former Member]

Hi Jayanthi,

Thanx for the Info. I can use Select..Distinct also. But there will be performance issue due to this select-endselect.

Normally.. select ..for all entries.. should eliminate the duplicates. But in my case its not doing. Some times..its working fine.but in other cases its extracting same record 3 to 5 times.

Im trying whether eliminating the duplicates from the driver table and sorting the driver table may resolve my problem.

Anyway.. Thankyou very much..for ur concern.

Message was edited by: Dead Lock

[jayanthi_jayaraman]

Hi,

I am not asking you to use select-endselect.I had just given the sample for DISTINCT.

Use Distinct in your select statement with the one you are using for for all entries.

select <b>Distinct</b> field into table internaltable2 for all entries in internaltable1 where field1 = internaltable1-field1.

Just check this sort of coding.

Have you checked?Is it working fine?

[Former Member]

If your internal table has fields that will result in duplicates, then they will be eliminated if you are using INTO TABLE option. The problem is not with the for all entries, it is how you build your internal table used in the 'for all entries'.

If you think your internal table has duplicates, you can always sort it and then do a DELETE ADJACENT DUPLICATES before you use the itab in the select statement.

Also, if you are using APPENDING TABLE ITAB instead of INTO ITAB, you will end up with large datasets.

Instead of us guessing, please post your select statements and the itab structures so that the forum can help.

Srinivas

[jayanthi_jayaraman]

Hi,

Just check the table is not initial and then write select statement.

if not internaltable1[] is initial.

select field into table internaltable2 for all entries in internaltable1 where field1 = internaltable1-field1.

endif.

*Then sort the internaltable2

sort internaltable2 by field.

delete adjacent dulpcates from internaltable2 comparing field.

Hope this helps.

Regards,

J.Jayanthi