Selection to join the tables VBFA and VBRK

Question

I have done a selection of the table VBFA, that has selected various SD documents based on the code below, and another selection of the table VBRK that has selected the documents that do not have a NETWR = 0. What I want to achieve is to select only those SD documents that are NETWR NE 0. The code for the selection of the table VBFA is as follows:

SELECT * FROM vbfa AS v INTO TABLE gt_vbfa_inv
      FOR ALL ENTRIES IN gt_vbak
      WHERE vbelv = gt_vbak-vbeln
        AND vbtyp_n IN ('M', 'O', 'P', '5', '6')
        AND stufe = '00'
        AND NOT EXISTS ( SELECT * FROM vbfa
                           WHERE vbelv = v~vbeln
                             AND posnv = v~posnn
                             AND vbtyp_n IN ('N', 'S')
                             AND stufe = '00' ) .

And the code for the selection of the VBRK table is as follows:

IF sy-subrc = 0.
      SELECT DISTINCT * FROM vbrk
        INTO TABLE gt_vbrk
        FOR ALL ENTRIES IN gt_vbfa_inv
        WHERE vbeln EQ gt_vbfa_inv-vbeln
          AND netwr NE 0.
  ENDIF.

Is there any way to merge these to two selection via a new Select or a Loop condition, that will select the documents of the table VBFA, where the documents will not have a NETWR = 0 (or NETWR NE 0)?

Thank you all in advance!

is your aim a performance improvement or readability improvement? They are not equal and getting rid of the multiple selects is not a panacea. You may achieve more just by getting rid of SELECT * and/or by CDS views — Suncatcher

Philipp Philipp · Accepted Answer · 2021-11-12T13:04:08

My standard approach for converting code using FOR ALL ENTRIES to a JOIN is this.

Convert it to the new SQL syntax with @ in front of every variable.
Replace the SELECT * with the fields I actually need. This is important, because JOINs between two tables will always have duplicate field. So SELECT * doesn't work. And it's a bad practice anyway. You almost never need all the fields, so SELECT * is almost always a waste of memory and CPU cycles.
Take both SELECTs and prefix every column name with the table. For example AND stufe = '00' becomes AND vbfa~stufe = '00'
Take the FOR ALL ENTRIES from the second and turn it into a JOIN-condition on the first select. So

   FROM vbrk
     FOR ALL ENTRIES IN @gt_vbfa_inv
     WHERE vbrk~vbeln = @gt_vbfa_inv-vbeln
       AND vbrk~netwr <> 0.

becomes

   JOIN vbrk
     ON vbrk~vbeln = vbfa~vbeln AND 
        vbrk~netwr <> 0.

Use an inline data declaration for the result-set. That way you don't need to manually create a table- and structure type for holding the results. The end-result would thus look something like this (untested code):

    SELECT vbfa~somefield1
           vbfa~somefield2
           vbfa~somefield3
           vbrk~somefield1
           vbrk~somefield2
       FROM vbfa 
       JOIN vbrk ON
            vbrk~vbeln = vbfa~vbeln AND 
            vbrk~netwr NE 0.
       INTO TABLE @DATA(gt_results)
          FOR ALL ENTRIES IN @gt_vbak
          WHERE vbfa~vbelv = gt_vbak-vbeln
            AND vbfa~vbtyp_n IN ('M', 'O', 'P', '5', '6')
            AND vbfa~stufe = '00'
            AND NOT EXISTS ( SELECT * FROM vbfa AS vbfa2
                               WHERE vbfa2~vbelv = vbfa~vbeln
                                 AND vbfa2~posnv = vbfa~posnn
                                 AND vbfa2~vbtyp_n IN ('N', 'S')
                                 AND vbfa2~stufe = '00' ) .

By the way: If gt_vbak comes from a SELECT too, then you likely can integrate this as another join here, too.

Selection to join the tables VBFA and VBRK

1 Answers