Performance DB SELECT * FOR ALL statement can be joined with SELECT statement at ... line ...

[SilasL]

Hey All,

I get the error in the title when I run performance db ATC test. I'm trying to get data from multiple tables using join. But since BSEG is a cluster table, I can't use join with it. So I made another statement using FOR ALL ENTRIES.

To simplify the code, I removed the joins in the first select so that it's easier to read. My original code also didn't have endselect for the second select, but making this simplified code. It kept complaining so I added it.

I thought the error wanted me to use a sub-query but you can only use subqueries for transparent tables which BSEG isn't.

Does anyone know what the ideal solution to this beside pseudo-comment. I'm a bit curious as I'm new to ABAP development and it seems like the code isn't wrong as it activates, and this error is only shown in ATC test.

SELECT  bukrs,
          belnr,
          gjahr
      FROM j_1bnfdoc
      INTO TABLE @DATA(lt_doc).

  IF lt_doc IS NOT INITIAL.
    SELECT  bukrs,
            belnr,
            gjahr
        FROM bseg
        INTO TABLE @DATA(lt_bseg)
        FOR ALL ENTRIES IN @lt_doc
        WHERE   bukrs = @lt_doc-bukrs
            AND belnr = @lt_doc-belnr
            AND gjahr = @lt_doc-gjahr.
  ENDIF.

Thanks,

Silas

[jens_michaelsen]

Why do you use the SELECT-ENDSELECT-statement for BSEG? Is that correct? I would prefer 'SELECT...INTO TABLE @DATA(lt_bseg)'.

With the SELECT...ENDSELECT you will get the last line in a workarea called 'LT_BSEG' only.

[benedict-torres]

Can you share the exact error here?

First, while it's syntactically correct, the usage of INTO a structure lt_bseg and FOR ALL ENTRIES is not performant. The performant way to do it is via FOR ALL ENTRIES... INTO TABLE @DATA(lt_bseg) then loop into the internal table. I guess this was just a mistake since you prefixed the variable with lt denoting a local internal table. This would trigger a performance db finding.

Secondly, the first SELECT statement would also trigger a performance DB finding as there's no WHERE condition.

[SilasL]

The exact error is

Description : SELECT * FOR ALL statement can be joined with SELECT statement at ... line ...

Check :Search SELECT .. FOR ALL ENTRIES-clauses to be transformed

This error is for the select on BSEG.

Yeah I forgot the TABLE keyword for INTO DATA when making the sample code, so I've fixed the code now.

So is it not ideal to select into an internal table right away? It would be better to loop it afterwards and then append it to a table?

[matt]

You need

IF lt_doc IS NOT INITIAL.
...
ENDIF.

around your second select.

[Sandra_Rossi]

It's the second question about exactly the same ATC message which is wrong because it doesn't apply because there's a cluster table. Which ATC variant do you use? Did you check the SAP notes to see whether a fix exists?

[SilasL]

My fault on the code. It should be INTO TABLE @DATA, so it's fixed now.

I ran the ATC test called performance_db.

I haven't looked into notes yet. I was mainly looking at SQL documentation for ABAP, seeing if there were specific notes for cluster tables.

[Sandra_Rossi]

For information, I couldn't find any fix about this check. You can't do much better (except if you want to use data which exist in the BS* "index" tables, they are transparent tables, not cluster tables, they are written at the same time as BSEG to improve the read performance). Hopefully, the concept of cluster tables has been removed from S/4HANA.