topic Re: using ranges statement in Application Development and Automation Discussions

using ranges statement

Former Member — Wed, 09 Sep 2009 13:50:47 GMT

I want to use ranges for a field condition in where clause

Please let me know the impact

the scenerio is the table on on whom select is done has indexes on several fields and one of the field is not in the where clause of the select which is giving performance problem hence the index is not hit

so can we declare a range statment and use in the where clause?

pls suggest how to use and create it

for eg i want to use ranges for below statement in where clause

SELECT * FROM vbak

INTO CORRESPONDING FIELDS OF iIt_itab

WHERE kunnr = lv_kunnr AND

vkorg = lv_vkorg AND

vtweg = lvvtweg .

now i want to insert a statement for ranges in where clause for a field eglike below

SELECT * FROM vbak

INTO CORRESPONDING FIELDS OF iIt_itab

WHERE kunnr = lv_kunnr AND

vkorg = lv_vkorg AND

vtweg = lvvtweg

autart = ...in ranges....

so how to code for this ranges statement request to pls suggest specifically to my requirement

regards

Nishant

Moderator message - Moved to the correct forum

Edited by: Rob Burbank on Sep 9, 2009 10:14 AM

Re: using ranges statement

Former Member — Wed, 09 Sep 2009 14:13:45 GMT

Try:

data: r_auart type range of vbak-auart.

Note - you have misspelled auart.

Rob

Re: using ranges statement

Former Member — Wed, 09 Sep 2009 15:21:14 GMT

HI Rob,

i tried to implement the same

SELECT * FROM vbak

INTO CORRESPONDING FIELDS OF It_tab

WHERE vkorg = lv_vkorg AND

Auart = r_auart AND "added line

kunnr = lv_kunnr AND

vtweg = lvvtweg AND

spart = lv_spart ANd

vbtyp = vbtyp_auftr

i added auart condition for ranges but it gives error..

r_auart cannot be a table , a reference , a string or contain any of thse object

pls suggest

regards

Nishant

Re: using ranges statement

amit_khare — Wed, 09 Sep 2009 15:23:32 GMT

Change

Auart = r_auart

With

Auart IN r_auart

Re: using ranges statement

Former Member — Wed, 09 Sep 2009 15:24:11 GMT

You're having some pretty basic problems. You have to use "IN" with a range, not "=".

Please try to find the problem yourself before asking the forum.

Rob

Re: using ranges statement

suredarreddy_pulimamidi — Wed, 09 Sep 2009 15:28:49 GMT

Hi,

Declare a range table as below.

DATA : ra_AUART TYPE RANGE OF AUART INITIAL SIZE 0,

wa_AUART like line of ra_AUART.

and fill the range table as below.

wa_AUART-sign = 'I'.

wa_AUART-option = 'EQ'i.

wa_AUART-low = ' '. "value

APPEND wa_AUART TO ra_AUART.

CLEAR wa_autyp.

the range table acts as select option and in select statement use IN ra_AUART.

Regards,

Surendar Reddy.

Re: using ranges statement

Former Member — Wed, 09 Sep 2009 15:29:55 GMT

yes rob my mistake..by mistake i took equal sign

thanks but can you please give me more details on range statement how does it work...and its implication

basically my main aim here is that with addiing auart now vbak secondry index will be hit which has vkorg auart and kunnr

so that will result in performance improvement for the select i wrot as this select is taking lot time to execute as no index is hit hence i did this any suggestion for the same or any other method to improve the performance of this select

regards

arora

Re: using ranges statement

Former Member — Wed, 09 Sep 2009 15:35:53 GMT

Why do you think adding AUART to the WHERE will help you use an index. It's not in any of the delivered indexes. Ot do you have a Z index?

Rob

Re: using ranges statement

Former Member — Wed, 09 Sep 2009 15:39:56 GMT

The RANGES is the same to the SELECT-OPTIONS only it needs to manage programmatically (the select-option is filled in the selection-screen).

So u can have:

TABLES VBAK.

SELECT-OPTIONS: S_AUART FOR VBAK-AUART.

SELECT * FROM VBAK  AUART IN S_VABK....

RANGES: R_AUART FOR VBAK-AUART.

SELECT * FROM VBAK  AUART IN R_VABK....

Max

Re: using ranges statement

Former Member — Wed, 09 Sep 2009 15:45:27 GMT


> The RANGES is the same to the SELECT-OPTIONS only it needs to manage programmatically (the select-option is filled in the >selection-screen).

Actually - that's not entirely correct.

SELECT-OPTIONS support CHECK, while ranges do not (at least in 4.7). There may bve other differences as well. Give it a try.

Rob

Re: using ranges statement

Former Member — Wed, 09 Sep 2009 15:50:51 GMT

yes Rob there is a zindex using vkorg auart and kunnr on the table

Re: using ranges statement

Former Member — Wed, 09 Sep 2009 15:53:30 GMT

Ok RoB

I'll try to be more precise, in this post we're talking about the range for a SELECT, aren't we?

IF it's so I believe a range and the select-option are the same, anyway I didn't want to speak about the RANGE in generally, but for SELECT only.

Max

Re: using ranges statement

Former Member — Wed, 09 Sep 2009 15:55:13 GMT

So it should be OK now. Just having KUNNR in the index should make it pretty selective - and that's what matters. You can use transaction ST05 to analyze this.

Rob

Re: using ranges statement

Former Member — Wed, 09 Sep 2009 15:56:32 GMT


> Hi
> 
> Ok RoB

Yes Max - I was just being picky

Rob

Re: using ranges statement

Former Member — Wed, 09 Sep 2009 15:58:30 GMT

hi MAx

actually my purpose of using range was to improve the performance

regards

Arora

Re: using ranges statement

Former Member — Wed, 09 Sep 2009 15:59:37 GMT

hi Rob

I will udapte on the same once i test it in st05

regards

Arora

Re: using ranges statement

ThomasZloch — Wed, 09 Sep 2009 16:06:20 GMT

Use table VAKPA for quickly accessing sales documents by partner number, together with a join on VBAK to get remaining information.

Thomas

Re: using ranges statement

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

Thanks Thomas

I will discuss on this with you tomorrow let me check first how much the improvement we get by the first range statement

then i can give you exact statement of the select for you to be able to give ur valuable suggestion for improving further on the performance....

regards

Nishant

Re: using ranges statement

faisalatsap — Wed, 09 Sep 2009 16:12:38 GMT

Hi, Arora

You must take care of this one too

Same Limitations for Select-Option Because bot work same way

and if you Values in Ranges are more than you will have to use FOR ALL ENTRIES

Regards,

Faisal

Edited by: Faisal Altaf on Sep 9, 2009 9:14 PM

Re: using ranges statement

Former Member — Wed, 09 Sep 2009 16:19:30 GMT


> I will discuss on this with you tomorrow let me check first how much the improvement we get by the first range statement

Thomas has given you the best solution. I wouldn't bother to to pursue the original way. If you have created the index for this particular SELECT, you can get rid of it.

Rob