Replace FOR ALL ENTRIES with Subselect

[casualCoder]

Hi Guys,

today the Code Inspector did warn me about Code that can be transformed.
Given ist this:

    SELECT objectid changenr udate FROM cdhdr
      INTO CORRESPONDING FIELDS OF TABLE ch_tab
      WHERE objectclas = gc_class_equi
         AND objectid   = equipment_nr
      ORDER BY PRIMARY KEY.

    IF lines( ch_tab ) > 0.
      SELECT mandant objectclas objectid changenr tabname tabkey fname chngind
        FROM cdpos
        INTO CORRESPONDING FIELDS OF TABLE cp_tab
        FOR ALL ENTRIES IN ch_tab
        WHERE objectclas = gc_class_equi
          AND objectid = ch_tab-objectid
          AND changenr = ch_tab-changenr
        ORDER BY PRIMARY KEY.
    ENDIF.

It suggests now that I can transform this into one Statement with Subselect, my questions are now:

• I need two Fields of above SELECT so I would need two Subselects now?
• Later the Programm needs the two internal tables seperately, is then a Subselect even possible
• How can I achieve this Subselect if its possible?

Kind regards

[Sandra_Rossi]

I guess you use S/4HANA, so CDPOS is not a cluster table, and then you can join CDHDR and CDPOS.

After the SELECT, you may initialize CP_TAB and CH_TAB if you really need it, but instead I would recommend to keep the internal table from the SELECT and refactor your program.

[chaouki_akir]

Can you show us the entire message of Code Inspector ?

May be SAP is asking you to do one SELECT joining CDHDR and CDPOS tables ?

[casualCoder]

No it says specifically to use Subselect. Joining CDHDR and CDPOS is not a good idea in R/3 Systems...

[Sandra_Rossi]

It's not that it's "not a good idea in R/3 Systems", it's that if CDPOS is a clustered table it's impossible to join CDPOS in ABAP SQL (which is not the case in S/4HANA as there are no more clusters).

I am really surprised that Code Inspector has a non-relevant check. Are you sure that CDPOS is defined as clustered? If it's so, then contact check SAP notes or contact SAP support.

[BiberM]

I just had a look to our System and it confirms what I had in the back of my head: the Pool and Cluster tables had been obsolete and got converted during Upgrade to EhP8 (NetWeaver 7.50).

And CDPOS is a transparent table in our EhP8 System so joining is possible.

[fedaros]

Maybe the suggestion came on S/4 system, which both tables are plain and a join could works fine.

[Sandra_Rossi]

Hopefully Tobias will confirm... (or not)

[A__R_]

Hi Tobias,

you may be able to solve your problem this way:

SELECT objectid, changenr, udate FROM cdhdr  
  INTO  TABLE @DATA(ch_tab)
    WHERE objectclas = gc_class_equi
    AND objectid   = equipment_nr
    ORDER BY PRIMARY KEY.

IF lines( ch_tab ) > 0.
  SELECT pos~mandant, pos~objectclas, pos~objectid, pos~changenr, pos~tabname, pos~tabkey, pos~fname, pos~chngind
    FROM cdpos AS pos
    INNER JOIN @ch_tab AS hdr
      ON  pos~objectclas = gc_class_equi
      AND pos~objectid = hdr~objectid
      AND pos~changenr = hdr~changenr
    INTO TABLE @DATA(cp_tab).

  SORT cp_tab.
ENDIF.

[casualCoder]

thank you, very interesting, but in my System (R/3 without HANA, NW 7.50) not possible yet

[patharesandesh]

If it is S/4, definitely simple join will work as cdpos is no longer a cluster table. Both are application tables.

[casualCoder]

hi again, now the System is not S/4 we have R/3 with NW 7.50. The topic with joining is interesting but not the Scope, the suggestion from SCI was explicitly to use subselect of CDHDR in SELECT CDPOS.

[Sandra_Rossi]

TL;DR "subselect" or "join" have same database performance because same or almost same execution, but you can't use them if CDPOS is clustered in your system (SE11 to check).

---------------------

You have said "we have R/3 with NW 7.50". I don't think it's possible to have R/3 with ABAP 7.50, only ECC or S/4HANA are possible with ABAP 7.50. As you say "NW 7.50" maybe you are talking of other software running aside your SAP ERP software, and it's useless information, we need to know your ABAP version (and so if it's ABAP 7.50 then it's more probably ECC).

I'm not an expert in how database runs the queries, but I think that subselect is an alternative to do joins, i.e. to write queries, and the database will basically execute them exactly the same way as joins. Also "subselect" in ABAP 7.50 is extremely limited (IN SELECT and EXISTS if I'm not wrong). Anyway, an ABAP "subselect" would not extract data from the other table, it can be only used in the WHERE, so join is required.

BTW you still didn't tell us if CDPOS is a transparent or clustered table in your system (look at SE11 > CDPOS > Attributes), and that is extremely important to know, because you won't be able to use neither subselect nor join.

For information, in ATC ABAP 7.57 with DEFAULT variant, indicates this message for your ABAP SQL:

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

Note that the concept of subselect exists in ABAP 7.57 but ATC says "joined".

For the fun, I compared the execution plan between a join and subselect (in S/4HANA and ABAP 7.57):

    SELECT cdhdr~objectclas, cdhdr~objectid, cdhdr~changenr, cdhdr~udate
        FROM cdhdr
        INNER JOIN cdpos
             ON cdpos~objectclas = cdhdr~objectclas
            AND cdpos~objectid = cdhdr~objectid
            AND cdpos~changenr = cdhdr~changenr
        WHERE cdhdr~objectclas = 'EQUI'
          AND cdhdr~objectid   = 'equipment_nr'
        INTO TABLE @DATA(itab1).

    SELECT cdhdr~objectclas, cdhdr~objectid, cdhdr~changenr, cdhdr~udate
        FROM cdhdr
        WHERE cdhdr~objectclas = 'EQUI'
            AND cdhdr~objectid   = 'equipment_nr'
            AND EXISTS (
                SELECT * FROM cdpos
                    where cdpos~objectclas = cdhdr~objectclas
                      AND cdpos~objectid = cdhdr~objectid
                      AND cdpos~changenr = cdhdr~changenr )
        INTO TABLE @DATA(itab2).

Result is respectively:

NB: the term "subselect" doesn't exist in the ABAP documentation, it's "subquery". I guess it means the same ("subselect" being a subquery inside a select).