topic Re: inner join in Application Development and Automation Discussions

inner join

Former Member — Wed, 24 Jan 2007 09:30:45 GMT

SELECT kna1_{kunnr kna1}ktokd INTO TABLE i_kna1keym

FROM ( kna1 INNER JOIN knvv ON kna1_{kunnr = knvv}kunnr )

WHERE kna1~kunnr IN s_kunnr AND

knvv~vkorg IN s_vkorg AND

kna1~ktokd IN r_ktokd AND

kna1~ktokd IN s_ktokd.

this is taking long time can any one help me how to make it faster.

Re: inner join

Former Member — Wed, 24 Jan 2007 09:33:14 GMT

Is the field KTOKD on on the index. If not you may be best to extract this field into your internal table then filter the records out from that. Also remove the condition from your select statement.

I also notice that you are check the value of KTOKD in two separate range tables.

Re: inner join

Former Member — Wed, 24 Jan 2007 09:33:51 GMT

Joins statements in your select would easily consume your time. I would suggest you to dump the data from both tab;es separately into 2 internal tables and then use your conditions to filter the records.

santosh

Re: inner join

Former Member — Wed, 24 Jan 2007 09:35:31 GMT

here why are you using kna1~ktokd two times i dont know... if you want to append some conditon you can append that in your selectoption also then why the range is there.

regards

shiba dutta

Re: inner join

Former Member — Wed, 24 Jan 2007 09:35:40 GMT

1 SELECT kna1_{kunnr kna1}ktokd INTO TABLE i_kna1keym

2 FROM ( kna1 INNER JOIN knvv ON kna1_{kunnr = knvv}kunnr )

3 WHERE kna1~kunnr IN s_kunnr AND

4 knvv~vkorg IN s_vkorg AND

5 kna1~ktokd IN r_ktokd AND

6 kna1~ktokd IN s_ktokd.

Whats the difference between 5th and 6th line.Can you please check it out.

Regards,

Re: inner join

Former Member — Wed, 24 Jan 2007 09:36:03 GMT

but the condition i am using is needed its extracting the data from only one table

Re: inner join

Former Member — Wed, 24 Jan 2007 09:37:19 GMT

Your final fetch is ktokd from KNA1.

select 1 into itab.

You can make use of table TVKO + KNVV for identifying the customer in the sales area .

2. for all entries of itab select 2 from kna1 into jtab with KTokd .

make use of this logic .

making use of index also is one quick access but u need to get approval for that if u get it then its okay else go with the logic .

You are doing validation on two tables one for sales orgnization with no matching primary /foreign keys to have a faster access.

so decrease the time access by tvko and knvv

next get the kna1-ktokd field with a single acces .

regards,

vijay

Re: inner join

Former Member — Wed, 24 Jan 2007 09:37:41 GMT

Try to change your code as below.

select kunnr ktokd from kna1 into table i_kna1keym
from kna1
where kunnr in ( select kunnr from knvv where kunnr in s_kunnr
                                                         and vkorg in s_vkorg)
and ktokd in r_ktokd
and ktokd in s_ktokd.

Also try to replace the two selection parameters for ktokd with one.

Manoj

Re: inner join

Former Member — Wed, 24 Jan 2007 09:46:54 GMT

Hi ,

Append r_ktokd dats into s_ktokd

Append s_ktokd dats into r_ktokd

and use any one in the select statement.

Regards,

Ashok kumar.

Re: inner join

Former Member — Wed, 24 Jan 2007 11:09:46 GMT

Try to use more key fields in the where condiiton like VKORG VTWEG

Re: inner join

Former Member — Wed, 24 Jan 2007 11:10:21 GMT

i am getting error in the code