select join statement

[Former Member]

Hi Dear all,

I have such statement in our report.

SELECT d~matnr d~werks e~lgort d~prvbe d~behaz d~behmg d~pknum
     INTO CORRESPONDING FIELDS OF TABLE gt_pvbe
     FROM ( ( pkhd AS d INNER JOIN pvbe AS e ON e~prvbe = d~prvbe AND e~werks = d~werks
            )
            INNER JOIN mard AS m
            ON d~matnr = m~matnr AND
               d~werks = m~werks AND
               e~lgort = m~lgort
           )
        WHERE d~werks = <lfs_outtab>-werks AND
              d~matnr = <lfs_outtab>-matnr AND
              e~lgort = <lfs_outtab>-lgort.

But ATC check give error that pvbe is buffered table, we should not using join with buffered table. Then I need to re-write this statement. I need to get table PVBE out of the join. Could any one have any idea how to re-write? Thank you so much.

BR

Jay

[Former Member]

Hi,

what you want is performance.

if you dont gain more performance when splitting it up, why would u want to do so? just because ATC tells something?

if you want the data from a table you will have to select it. with "select statement". you can try to reduce this select querry to a minimum. for example dont do 1000 selects in a loop. but if you need it i the loop for better performance, you need it. thats why there are Tags, to remove the ATC-Check when you think the check tells somewinth wrong.

just write following behind your select querry.

"#EC CI_BYPASS

regards

Stefan Seeburger

[Former Member]

Hi Seeburger,

Actually, as you could see, it is in loop, we have already set 'Do...times' to it. And to comment "#EC CI_BYPASS at the end of the codes is forbidden in our company. It is useless to help improve performance.

BR

Jay

[former_member184158]

Hi,.

I recommand you to ignore the ATC check, and you can select it as you want,

there is no syntay error in you select.

Regards

Ibr

[Former Member]

Hi Ibr,

ATC is required to check before release in our company

[former_member184455]

Hi,

Why is table PVBE in the JOIN? The field LGORT is also in table MARD.
Where does the data in <lfs_outtab> come from? It looks like you do this JOIN inside some LOOP, this could become quite slow in execution.

Regards, Randolf

[former_member184158]

Hi,

you are right,  he is doing select inside loop. and not select single, he is doing, select into table,

this causes bad performance .

Regards

Ibr

[Former Member]

Hi Randolf,

Thank you for your reply. I think your advise is the most convenient method. Thank you.

BR

Jay

[Richa_Gupta]

SELECT d~matnr d~werks e~lgort d~prvbe d~behaz d~behmg d~pknum

     INTO CORRESPONDING FIELDS OF TABLE gt_pvbe

     FROM ( ( pkhd AS d INNER JOIN pvbe AS e ON e~prvbe = d~prvbe AND e~werks = d~werks

            )

            INNER JOIN mard AS m

            ON d~matnr = m~matnr AND

               d~werks = m~werks AND

               e~lgort = m~lgort

           )

        WHERE d~werks = <lfs_outtab>-werks AND

              d~matnr = <lfs_outtab>-matnr AND

              e~lgort = <lfs_outtab>-lgort.

Lets say your loop is on internal table I_TAB.

So, put a select on PRVBE for all entries in I_TAB where LGORT = I_TAB-LGORT. Lets say you populate I_PRVBE using this.

Now, put a select on PKHD for all entries in I_TAB where werks = I_TAB-WERKS and matnr = i_TAB-MATNR.  Lets say you populate I_PKHD using this.

I am not seeing any use of MARD in above SELECT as you are not selecting anything from it not it is in your WHERE clause.

Now, inside the loop.

LOOP AT I_TAB ASSIGNING <FS>.

READ TABLE I_PKHD INTO W_PKHD WITH KEY WERKS = <FS>-WERKS MATNR = <FS>-MATNR.

IF SY-SUBRC = 0.

     wt_pvbe-matnr = w_pkhd-matnr.

     wt_pvbe-werks = w_pkhd-werks.

      wt_pvbe-prvbe = w_pkhd-prvbe.

     wt_pvbe-behaz = w_pkhd-behaz.

     wt_pvbe-behmg = w_pkhd-behmg

     wt_pvbe-pknum = w_pkhd-pknum

     READ TABLE PRVBE INTO W_PRVBE WITH KEY LGORT = <FS>-LGORT.

     IF SY-SUBRC = 0.

          wt_pvbe-lgort = w_prvbe-lgort.

      ENDIF.

append wt_pvbe to gt_pvbe.

clear : wt_pvbe.

ENDIF.

ENDLOOP.

As I have just typed in above code, so it is not optimized. Don't forget to select just those fields which are required for the processing. Also, don't forget to pur a sy-subrc check after every READ statement, etc.

Regards,

Richa

[Jelena_Perfiljeva]

I can only agree with the previous comments that ATC (=ABAP Test Cockpit, just saved you a trip to Google ) complaint is the least of the worries here. Benefits of using JOIN could far outweigh the fact that buffer would be bypassed.

But it does seem that your SELECT is not very well written to begin with and also might be within a LOOP, which is usually a no-no, so that's what you might want to look into. Also - don't use aliases ('as D"), just use the table name, like pkhd~matnr. Aliases are needed only in some specific cases, otherwise they are just a distraction.

[raphael_almeida]

Adding what said, I believe it is necessary to separate this query too, I did not find it very performative (correct me if I'm wrong oks?).

Warm regards,

Raphael Pacheco.

[Former Member]

Hi Pacheco,

As Randolf said, The field LGORT is also in table MARD. So I just removed the table PBVE out of the select statement. Please give me hints if you have better performance solution. Thank you.

SELECT d~matnr d~werks m~lgort d~prvbe d~behaz d~behmg d~pknum
     INTO CORRESPONDING FIELDS OF TABLE gt_pvbe
     FROM ( pkhd AS d INNER JOIN mard AS m
            ON d~matnr = m~matnr AND
               d~werks = m~werks )
       FOR ALL ENTRIES IN gt_outtab
        WHERE d~werks = gt_outtab-werks AND
              d~matnr = gt_outtab-matnr AND
              m~lgort = gt_outtab-lgort.