using ranges statement

[Former Member]

hi

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

[Former Member]

Try:

data: r_auart type range of vbak-auart.

Note - you have misspelled auart.

Rob

[Former Member]

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

[amit_khare]

Change

Auart = r_auart

With

Auart IN r_auart

[Former Member]

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

[suredarreddy_pulimamidi]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

Hi

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

[Former Member]

>

> 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

[Former Member]

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

[Former Member]

Hi

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

[Former Member]

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

[Former Member]

>

> Hi
> 
> Ok RoB

Yes Max - I was just being picky

Rob

[Former Member]

hi MAx

actually my purpose of using range was to improve the performance

regards

Arora

[Former Member]

hi Rob

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

regards

Arora

[faisalatsap]

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

[ThomasZloch]

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

Thomas

[Former Member]

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

[Former Member]

>

> 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

[Former Member]

HI Rob,

Thanks i can understand that but i need to explore more on the select statement may be i give u my proper select statement with addition of ranges then probably Thomas or you can suggest me the solution for the same and the index is already there used for some other purpose i am just using it and i canot delete that.

regards

Arora

[Former Member]

here is the select for which i wrote above

SELECT vkorg vtweg spart kunnr vbeln vbtyp erdat erzet

FROM vbak

INTO CORRESPONDING FIELDS OF It_itab

WHERE vkorg = lv_vkorg AND

Auart in ranges_auart AND "this i have added for the index and ranges

kunnr = lv_kunnr AND

vtweg = lv_vtweg AND

spart = lv_spart AND

vbtyp = vbtyp_auftr

ORDER BY erdat DESCENDING erzet ASCENDING.

pls let me know the other way to optimize it by using other table if ranges is not good enough....

regards

arora

[Former Member]

Have a look at the key fields of VBAK and VAKPA. The relationship is on the document number. Try coding this yourself and get back to the forum if you have problems.

Rob

[Former Member]

answered