topic Re: select join statement in Application Development and Automation Discussions

select join statement

Former Member — Fri, 21 Aug 2015 08:16:19 GMT

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.

Jay

Re: select join statement

Former Member — Fri, 21 Aug 2015 08:27:55 GMT

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

Re: select join statement

former_member184158 — Fri, 21 Aug 2015 10:05:45 GMT

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

Re: select join statement

former_member184455 — Fri, 21 Aug 2015 14:51:56 GMT

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

Re: select join statement

former_member184158 — Fri, 21 Aug 2015 15:59:31 GMT

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

Re: select join statement

Richa_Gupta — Fri, 21 Aug 2015 17:58:44 GMT

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

Re: select join statement

Jelena_Perfiljeva — Fri, 21 Aug 2015 19:07:23 GMT

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.

Re: select join statement

raphael_almeida — Fri, 21 Aug 2015 19:31:00 GMT

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.

Re: select join statement

Former Member — Tue, 25 Aug 2015 06:19:15 GMT

Hi Randolf,

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

Jay

Re: select join statement

Former Member — Tue, 25 Aug 2015 06:23:12 GMT

Hi Ibr,

ATC is required to check before release in our company

Re: select join statement

Former Member — Tue, 25 Aug 2015 06:25:59 GMT

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.

Jay

Re: select join statement

Former Member — Tue, 25 Aug 2015 07:19:58 GMT

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.