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

join statement.

Former Member — Wed, 16 Sep 2009 10:57:47 GMT

Hi ,

I have given following statement.

select vbak~vbeln

vbak~bstdk

vbak~bstnk

vbak~knumv

vbak~lifsk

vbak~augru

vbak~erdat

vbak~spart

vbak~vkbur

vbap~arktx

zro_downpayment~branch_date

zro_downpayment~bldat

zro_downpayment~belnr

zro_downpayment~payment_scheme

zro_downpayment~payable_to

zro_downpayment~payment_method

zro_downpayment~contract

zro_downpayment~payee

zro_downpayment~supplier

zro_downpayment~soldto

into table it_vbak

from vbak left outer join vbap on vbak_{vbeln = vbap}vbeln

left outer join zro_downpayment on vbak_{vbeln = zro_downpayment}vbeln

where vbak~erdat in s_erdat

and vbak~lifsk eq c_lifsk1

and vbak~vkorg eq p_vkorg

and vbak~vtweg in s_vtweg

and vbak~spart in s_spart

and vbak~vkbur in s_off.

in the above statement vbak_{erdat is used only 15-20% time i.e. user generally does not enter values in s_erdat. Index of vbak is on erdat field. Can you please guide me how to tune this statement to avoid vbak}erdat and use different where clause so that program is executed efficiently.

Regards,

Santosh

Re: join statement.

Former Member — Wed, 16 Sep 2009 11:32:37 GMT

Hi,

Please use Views instead of joins.

Or Use seperate select query join for vbak and vbap and seperate query for ztable.

Avoid using left outer joins.

Regards,

Nandha

Re: join statement.

Former Member — Wed, 16 Sep 2009 11:47:18 GMT

Hi Santosh ,

As per my understanding of your logic , you need to fetch records from zro_downpayment based on VBAK and VBAP entries based on some conditions ,

There are many ways to do the same , you need to identify the best in your case.

You can first fetch the records from VBAK and VBAP in one go in an internal table .

Now get the unique VBELN from this internal table.

Use FOR ALL ENTRIES to fetch records from database table zro_downpayment .

Now, collect all the records in resultant internal table .

Hope this helps you.

Note : This can increase the code processing time , but will reduce time for database operation.

Also, use of join with more than 2 tables should not be prefered.

Re: join statement.

Former Member — Wed, 16 Sep 2009 12:12:29 GMT


> Use FOR ALL ENTRIES to fetch records from database table zro_downpayment .
> Note : This can increase the code processing time , but will reduce time for database operation.
> 
> Also, use of join with more than 2 tables  should not be prefered.

I'm curious to know how you came to this conclusion that Join is not preffered for more than 2 tables and FOR ALL entries is better in database operation.

BR,

Advait

Re: join statement.

Former Member — Wed, 16 Sep 2009 12:53:00 GMT

A few pointers

- You've joined VBAK and VBAP , but are not selecting the posnr field anywhere. This may lead to inconsistencies.

- There is no index on VBAP so it is diffcult to refine your query.

Apart from that since you are using an outer join, the db system creates a temporary table with all the records as per the ON condition, then fetch all remaining records from the left hand side table and put zero values in the fields of the right hand side table. And then applies the where condition.

Thus for outer joins,the number of records fetched will be higher if the fields in the where clause are left blank.

Only thing that I can think of it to make the erdat mandatory. But not a very good solution perhaps.

Hope someone can come up with a better solution.

BR,

Advait

Re: join statement.

Former Member — Wed, 16 Sep 2009 22:43:51 GMT

Hi Santosh,

You have told us that the user only enters ERDAT 15-20% of the time however you have not told us what the user enters most of the time. Usually you need to inform your users that they cannot run the report open (without any selection criteria) online. If they insist on doing that tell them to run the program in the background.

With regards to VBAK you don't have much choice. SAP provides secondary indexes in ERDAT and AUDAT in addition to the primary index on VBELN. You need to talk to you users and explain to them this problem.