Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Select FOR ALL ENTRIES statement cannot get duplicates entries

siongchao_ng
Contributor
0 Kudos

Hi all,

t_yxapy_fields consist of

inty field

0001 kostl

0002 perid

0008 ansal

0008 preas

0008 trfst

The following is the code for me to transfer the contents of t_yxapy_fields into t_pa_fields.

SELECT dbtab

FROM t777d

INTO TABLE t_pa_fields

FOR ALL ENTRIES IN t_yxapy_fields

WHERE infty = t_yxapy_fields-infty.

And t_pa_fields just ended up with

field infty

pa0001

pa0002

pa0008

Question:The first table have 3 similar infotype 0008 but the result table only have one pa0008. I need to filled up acordingly as what table 1 have. I need another 2 pa0008 in my 2nd table. What is wrng with my Select for all entries statement here?

Edited by: Siong Chao on Mar 27, 2010 10:35 AM

1 ACCEPTED SOLUTION

Former Member
0 Kudos

It is little bit tricky.

I also have the same scenario where my internal table will have duplicate and I need to append all the records including the dulpicates record into another table.

FOr this u can use SELECT ..>ENDSELECT.

loop at itab1.

select .....

into corresponding fields of wa_itab2

from ...

where ....

append wa_itab2 to itab2.

endselect.

endloop.

hope this helps

16 REPLIES 16

Former Member
0 Kudos

This is a feature of the [FOR ALL ENTRIES|http://help.sap.com/abapdocu_70/en/ABENWHERE_LOGEXP_ITAB.htm] condition, e.g. see ABAP help:

Lines that appear in duplicate are automatically removed from the result set.

So to avoid duplicate removal, the only way is to ensure that there are no duplicates by adding more fields. In general this is only possible when selecting all key fields, but depending on the scenario sometimes other field combinations are possible.

0 Kudos

Hi,

SELECT dbtab

FROM t777d

INTO TABLE t_pa_fields

FOR ALL ENTRIES IN t_yxapy_fields

WHERE infty = t_yxapy_fields-infty.

This has nothing to do with duplicates in t777d. I mean now, table t_yxapy_fields have 3 '0008' . My Select statement there only can produce 1 "PA0008' in table t_pa_fields. Any idea to select all 1st column entries from table t_yxapy_fields into t_pa_fields?

0 Kudos

This has nothing to do with duplicates in t777d.

Correct, I failed to recognize that table t777d has only infty as key as pointed out by Aby. Thus in your particular case my comment was misleading. In general though FOR ALL ENTRIES removes all duplicate rows from your result set.

If you want the other 2 values just loop t_yxapy_fields this table and read t_pa_fields and if record found add the other values to t_pa_fields table. --> will trigger performance tuning issue

I'm pretty sure if you'd code this, you won't run into any performance issues, so follow Aby's recipe. To be more concrete, here's what I'd do: Define a mapping table t777d_tab, which holds the translation from infty to dbtab:


types:
  begin of T_T777D_VIEW,
    INFTY type T777D-INFTY,
    DBTAB type T777D-DBTAB,
  end of T_T777D_VIEW.
data:
  T777D_TAB type hashed table of T_T777D_VIEW with unique key INFTY.

Fill table t_pa_fields, typed using t_t777d_tab from above, from table t777d. Then loop over your table t_yxapy_fields (I'd use [LOOP AT ... ASSIGNING|http://help.sap.com/abapdocu_70/en/ABAPLOOP_AT_ITAB_RESULT.htm] instead of LOOP AT ... INTO) and for each entry get the corresponding DBTAB value from t777d_tab (again use the [READ TABLE ... ASSIGNING|http://help.sap.com/abapdocu_70/en/ABAPREAD_TABLE_OUTDESC.htm] variant) and build your result table t_pa_fields.

Anyhow, I suspect that even with less optimal coding (e.g. using just standard table for lookup of DBTAB values) you'd not see much of an performance impact, because it seems that your internal tables are most likely rather small...

Cheers, harald

0 Kudos

Hi Harald

Your statement:

Fill table t_pa_fields, typed using t_t777d_tab from above, from table t777d. --> The part here already trigger the duplicates delete we are discussin here.

The following codes here unable to fill duplicates inty into t_pa_fields.

SELECT dbtab

FROM t777d

INTO TABLE t_pa_fields

FOR ALL ENTRIES IN t_yxapy_fields

WHERE infty = t_yxapy_fields-infty.

Your statement:

Then loop over your table t_yxapy_fields (I'd use LOOP AT ... ASSIGNING instead of LOOP AT ... INTO) and for each entry get the corresponding DBTAB value from t777d_tab (again use the READ TABLE ... ASSIGNING variant) and build your result table t_pa_fields.

The following is what I code based on your loop suggestion.

LOOP AT t_yxapy_fields INTO w_yxapy_fields.

IF w_yxapy_fields-infty < 1000.

CONCATENATE 'PA' w_yxapy_fields-infty INTO v_str.

ELSE.

SELECT SINGLE dbtab --> ( If I have let say 2 duplicates infty 1000, will it be fetched into v_str since I do not use FOR ALL ENTRIES in this statement ? )

FROM t777d

INTO v_str WHERE infty = w_yxapy_fields-infty.

ENDIF.

IF sy-subrc = 0.

w_pa_fields-pa = v_str.

w_pa_fields-field = w_yxapy_fields-field.

w_pa_fields-req = w_yxapy_fields-req.

w_pa_fields-sign = w_yxapy_fields-sign.

w_pa_fields-opt = w_yxapy_fields-opt.

w_pa_fields-low = w_yxapy_fields-low.

w_pa_fields-high = w_yxapy_fields-high.

w_pa_fields-active = w_yxapy_fields-active.

APPEND w_pa_fields TO t_pa_fields.

CLEAR w_pa_fields-pa.

ENDIF.

ENDLOOP.

Rgds,

Siong Chao

Edited by: Siong Chao on Mar 29, 2010 4:34 AM

Edited by: Siong Chao on Mar 29, 2010 4:48 AM

Former Member
0 Kudos

Hi Chao,

Your select query is absolutely working fine , nothing wrong with it,

In Table T777D , it has only 1 primary key INFTY , so no duplicate entries are in T777D table,

If you want the other 2 values just loop t_yxapy_fields this table and read t_pa_fields and if record found add the other values

to t_pa_fields table.

Regards,

Aby.

0 Kudos

Hi,

The select t777d purpose is to concatenate whatever alphabet name into the infotype digit. ( eg: 0001 -> PA0001 and so on)

For my case here, 1st table have 3 infotype 0008, my select statement can only load one of the 0008 into the 2nd table. I need to load whatever number of infotypes there is in 1st table into the 2nd table.

According to your statement :

If you want the other 2 values just loop t_yxapy_fields this table and read t_pa_fields and if record found add the other values

to t_pa_fields table. --> will trigger performance tuning issue

My Select statement purpose is to read db table t777d to concatenate the alphabets into all infotype entries (1st column of 1st table) one time for performance tuning purpose. I need my select statement to work properly here.

Edited by: Siong Chao on Mar 27, 2010 2:08 PM

VivekG
Participant
0 Kudos

Hi Siong,

First of all as T777D table has only INFTY as primary key so there is no question of duplicate entries coming out of this select statement.

Please let me know why do u want duplicate entries in your table. If you still want it then loop one table and read other and append the entries in the table.

Hope it helps.

Vivek

0 Kudos

Hi Vivek,

You ask why I need duplicates entries? Consider the following 2 tables.

t_yxapy_fields consist of

inty field

0001 kostl

0002 perid

0008 ansal

0008 preas

0008 trfst

And t_pa_fields just ended up with

field infty

pa0001

pa0002

pa0008

Now you noticed when I use the Select for all entries to concatenate the db name onto the list of infotypes in table t_yxapy_fields, I only received 1 entries pa0008 in table t_pa_fields. This is wrong. I need whatever number of entries in table t_yxapy_fields to be in table t_pa_fields.

Edited by: Siong Chao on Mar 29, 2010 10:17 AM

0 Kudos

You are following the wrong approach. FAE will delete all duplicates, there is no way of avoiding this.

I would follow Haralds approach of defining a lookup table for the relation infotype => database table.

And still i'm wondering if there is really a HR setup where the realtion nnnn => PAnnnn is not valid, except the OM tables which start with HRP.

So, in reality you will always have nnnn => PAnnnn or nnnn => HRPnnnn. I would really like to see a setup where the salary table is not PA0008.

0 Kudos

Hi Rainer,

I dont quite get you onthe following:

And still i'm wondering if there is really a HR setup where the realtion nnnn => PAnnnn is not valid, except the OM tables which start with HRP.

So, in reality you will always have nnnn => PAnnnn or nnnn => HRPnnnn. I would really like to see a setup where the salary table is not PA0008.

This whole thing is about performing table fields checks for selected set of pernrs and the infotypes and fields to be checked for certain values like initial and any specific values is maintained in a custom table.

You mean to say I cannot check infotype more than 1000 for this case?

siongchao_ng
Contributor
0 Kudos

Hi all,

As a recap for all the discussions prior, we know here FOR ALL ENTRIES will delete duplicates result set. So, I am omitting the FOR ALL ENTRIES. In addition, suggestion to loop the table has also been received, but the initial idea of using the Select statement (refer to my first post) is to read table t777d ONE TIME (performance purpose) to produce the field column in table t_pa_fields based on what we have in column infty in table t_yxapy_fields.

t_yxapy_fields consist of

inty field

0001 kostl

0002 perid

0008 ansal

0008 preas

0008 trfst

And t_pa_fields just ended up with

field infty

pa0001

pa0002

pa0008

Ideally, t_pa_fields should consist of

field infty

pa0001

pa0002

pa0008

pa0008

pa0008

Before I close this thread (current solution is the looping code i posted earlier),

Is there any Select statement that can be used to produce the "ideal table t_pa_fields" shown above?

0 Kudos

Why not just read complete t777d into hash table, loop over your internal table and do a read inside? read table on a hashed one is incredible fast.

Former Member
0 Kudos

I don't think this problem has anything to do with FAE deleting duplicate entries.

Rob

Former Member
0 Kudos

It is little bit tricky.

I also have the same scenario where my internal table will have duplicate and I need to append all the records including the dulpicates record into another table.

FOr this u can use SELECT ..>ENDSELECT.

loop at itab1.

select .....

into corresponding fields of wa_itab2

from ...

where ....

append wa_itab2 to itab2.

endselect.

endloop.

hope this helps

0 Kudos

...and you would end up SELECTing the same record multiple times.

Rob

0 Kudos

I really do not understand the problem here. There is really no reason to think about how to solve this via one select statement.

do it this way:

types: begin of t_infty,
  infty type infotype,
  dbtab type dbtabl,
end of t_infty.

data:
  it_inflookup type hashed table of t_infty with unique key infty.

field-symbols:
  <lookup> type t_infty.

start-of-selection.
  select infty dbtab
  into table it_inflookup
  from t777d.
.
.
.
.
  loop at it_tab assigning <ptab>.
    read table it_inflookup with table key infty = <ptab>-infty assigning <lookup>.
    if sy-subrc = 0.
      <ptab>-dbtab = <lookup>-dbtab.
    else.
      message 'Unknown inftype' type 'A'.
    endif.

  endloop.

Using a hash table and a fieldsymbol this is really performant and not worth to think about from an performance point of view. If you do not need the lookup table anamore you can free that after the loop releasing the memory.

sorry rob, was not my intention to answer to your reply.

Edited by: Rainer Hübenthal on Mar 30, 2010 3:14 PM