How to access the data in the table used for the FOR ALL ENTRIES statement?

Question

I need some data from the tables BKPF and BSEG compressed in one table. For example let's say the final table has to look something like this:

| BKPF-BUKRS | BKPF-GJAHR | BKPF-MONAT | BSEG-DMBTR |
|============|============|============|============|
|         01 |       2014 |         02 |   1,751.55 |
|         01 |       2014 |         02 |     526.45 |
|         02 |       2014 |         02 |  32,112.01 |
|            |            |            |     ...... |
|============|============|============|============|

To make the program efficient I wanted to use the statement FOR ALL ENTRIES. So it looked like this:

SELECT BUKRS BELNR GJAHR MONAT 
  FROM BKPF 
  INTO CORRESPONDING FIELDS OF TABLE IT_BKPF
  WHERE .... 

IF IT_BKPF IS NOT INITIAL. 
  SELECT DMBTR
    FROM BSEG 
    INTO CORRESPONDING FIELDS OF LS_BSEG 
    FOR ALL ENTRIES IN IT_BKPF 
    WHERE BUKRS = IT_BKPF-BUKRS AND 
          BELNR = IT_BKPF-BELNR AND 
          GJAHR = IT_BKPF-GJAHR. 

    LS_RESULT-BUKRS = LS_BSEG-BUKRS.
    LS_RESULT-GJAHR = LS_BSEG-GJAHR.
    LS_RESULT-MONAT = ???
    LS_RESULT-DMBTR = LS_BSEG-DMBTR.

    COLLECT LS_RESULT INTO IT_RESULT.
  ENDSELECT.
ENDIF.

Problem now is: How do I get the value of BKPF-MONAT for each entry? Is there a way to do this with FOR ALL ENTRIES? Another solution would be two SELECT loops, something like:

SELECT BUKRS BELNR GJAHR MONAT 
  FROM BKPF 
  INTO CORRESPONDING FIELDS OF LS_BKPF
  WHERE .... 

  SELECT DMBTR
    FROM BSEG 
    INTO CORRESPONDING FIELDS OF LS_BSEG 
    WHERE BUKRS = LS_BKPF-BUKRS AND 
          BELNR = LS_BKPF-BELNR AND 
          GJAHR = LS_BKPF-GJAHR. 

    LS_RESULT-BUKRS = LS_BKPF-BUKRS.
    LS_RESULT-GJAHR = LS_BKPF-GJAHR.
    LS_RESULT-MONAT = LS_BKPF-MONAT.
    LS_RESULT-DMBTR = LS_BSEG-DMBTR.

    COLLECT LS_RESULT INTO IT_RESULT.
  ENDSELECT.
ENDSELECT.

But I assume this isn't very efficient.. Normally you would use a JOIN for this but AFAIK you can't do that with a cluster table (BSEG).

Are you absolutely sure using `FOR ALL ENTRIES` will make the program more efficient? — Jagger, Jun 03 '14 at 13:48
@Jagger If the table you're `FOR ALL ENTRIES`-ing on is of type sorted table, it will. — Raven Dreamer, Jun 03 '14 at 15:03
@RavenDreamer I am not so sure whether sorting helps in this case. It would be interesting to see how this Open-SQL-Query is actually translated to SQL-Query of the underlying database. [Here](http://blogs.msdn.com/b/saponsqlserver/archive/2011/12/28/abap-select-for-all-entries-what-is-happening-in-sql-server.aspx) is an interesting article which shows how it looks like for the SQL Server. It looks like there are several (depending on how big the `FOR ALL ENTRIES` table is) `SELECT` statements created and unified, sorting does not help in such case. — Jagger, Jun 03 '14 at 15:18
@Jagger Not sort a table, *type* sorted table. The database has no way of knowing that any given parameter is sorted (which can result in faster more efficient queries) unless it is passed a type "sorted table". — Raven Dreamer, Jun 06 '14 at 12:26
@RavenDreamer As I said because of the way the statement is translated to DB SQL it does not matter what kind of table it is. — Jagger, Jun 07 '14 at 07:49

Jagger · Accepted Answer · 2014-06-03T15:29:50.580

Try to read the entry from IT_BKPF inside the SELECT. ENDSELECT.

For example like this:

FIELD-SYMBOLS: <fs_str_bkpf> LIKE LINE OF it_bkpf.

SELECT.
...
  READ TABLE it_bkpf WITH KEY bukrs = ls_bseg-bukrs belnr = ls_bseg-belnr
    gjahr = ls_bseg-gjahr ASSIGNING <fs_str_bkpf>.
...
    ls_result-monat = <fs_str_bkpf>-monat.
ENDSELECT.

And let the table IT_BKPF be a HASHED TABLE or at least SORTED TABLE then you should have no bigger performance issues.

And here is some working example (ignore CLIENT SPECIFIED, I had to use it because there was no data in the client I was working in)...

DATA: lt_bkpf TYPE HASHED TABLE OF bkpf
  WITH UNIQUE KEY mandt bukrs belnr gjahr.
DATA: ls_bseg TYPE bseg.
TYPES: BEGIN OF t_result,
  bukrs TYPE bkpf-bukrs,
  gjahr TYPE bkpf-gjahr,
  monat TYPE bkpf-monat,
  dmbtr TYPE bseg-dmbtr,
END OF t_result.
DATA: ls_result TYPE t_result.
DATA: lt_result LIKE TABLE OF ls_result.
FIELD-SYMBOLS: <fs_str_bkpf> LIKE LINE OF lt_bkpf.

SELECT mandt bukrs belnr gjahr monat
  UP TO 10 ROWS
  FROM bkpf CLIENT SPECIFIED
  INTO CORRESPONDING FIELDS OF TABLE lt_bkpf.

IF NOT lt_bkpf IS INITIAL.
  SELECT dmbtr
    FROM bseg CLIENT SPECIFIED
    INTO CORRESPONDING FIELDS OF ls_bseg
    FOR ALL ENTRIES IN lt_bkpf
    WHERE bukrs = lt_bkpf-bukrs AND
          belnr = lt_bkpf-belnr AND
          gjahr = lt_bkpf-gjahr AND
          mandt = lt_bkpf-mandt.

    MOVE-CORRESPONDING ls_bseg TO ls_result.
    READ TABLE lt_bkpf WITH KEY bukrs = ls_bseg-bukrs belnr = ls_bseg-belnr
      gjahr = ls_bseg-gjahr ASSIGNING <fs_str_bkpf>.

    ASSERT sy-subrc = 0 AND <fs_str_bkpf> IS ASSIGNED.

    ls_result-monat = <fs_str_bkpf>-monat.
    COLLECT ls_result INTO lt_result.
  ENDSELECT.
ENDIF.

I thought about that too, but feard that the additional read of the table would be a performance issue. As you said the HASHED or SORTED statement should do the trick. [Thanks alot](http://th00.deviantart.net/fs70/150/f/2011/108/b/f/thanks_alot_by_kiki_akuma-d3eafmb.png)! — cheeZer, Jun 04 '14 at 14:14

How to access the data in the table used for the FOR ALL ENTRIES statement?

1 Answers1