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

regarding select statement

Former Member — Sat, 04 Aug 2007 04:15:21 GMT

hi experts,

here in the given select statement the last select statement is giving runtime error i.e '' Time limit exceeded".kindly help me to solve this problem sud i use indexes for this but how........

select mblnr

mjahr

blart

budat

from mkpf into

table itab_mkpf_rcvdate where

budat between itab_mkpf-p_date1 and itab_mkpf_rcvdate-p_date2

and blart in

('WA','WE','WL').

select matnr

erfmg

mblnr

ebeln

werks

lgort

bwart

lfbnr

smbln from mseg into table itab_101

for

all entries in itab_mkpf_rcvdate

where mblnr = itab_mkpf_rcvdate-mblnr and

bwart in ('101','531') and werks = s_werks.

<b>select

bwart

matnr

ebeln

lfbnr

smbln from mseg

into corresponding fields of table itab3 for

all entries in itab_101 where ebeln =

itab_101-ebeln and bwart in ('102','532')

.</b>

Re: regarding select statement

Former Member — Sat, 04 Aug 2007 04:37:40 GMT

Hi,

Find out if you can pass any primary key while selecting the data from mseg.Keeping performance issues in mind it is always advisable, not to hit the same database table twice and you are hitting it twice.Check out if possible for you to retrieve all the data from MSEG once into a internal table.

Also Select * from dtab into correspondind field is not recommened from performance point of view.So while retrieving the data try to use an internal table with the same structure for the fields you are selecting from the table.

For how to improve the performance by keeping the same code, for all the Indexes already present in the table find out whether you can pass one more field from the index or not.If not than you can go with creation of indexes.But generally as far as possible try not to create a new index.

Reward if useful

Thanks,

Mohit

Re: regarding select statement

Former Member — Sat, 04 Aug 2007 05:03:43 GMT

Hi,

You can use Material Document year to restrict the entries in the select query or still if it is timing out you can try to run the report in back ground and then retrive the result from the spool once a year report are moslty run in back ground as they have huge amount of data and simple select query may take a lot of time to work causing a time out .

Regards,

Himanshu

Re: regarding select statement

Former Member — Sat, 04 Aug 2007 05:24:31 GMT

MSEG table contains lot of data and you are fetching it based on just ebe;ln field

why can't you write a single select statment by joining MKPF and MSEG and then seggragate the data into different internal tables based on your conditions.

select

a~mblnr

a~mjahr

a~blart

a~budat

b~bwart

b~matnr

b~erfmg

b~ebeln

b~werks

b~lgort

b~lfbnr

b~smbln

from mkpf as a join mseg as b on a_{mblnr = b}mblnr and

a_{mjahr = b}mjahr

into table itab

a~budat ( between date1 and date2 )

and a~mjahr = s_year

and a~blart in ('WA','WE','WL')

and b_{bwart in ( '101','531' ,'102','532' ) and b}werks = s_werks.

<b>Reward points for useful Answers</b>

Regards

Anji

Re: regarding select statement

Former Member — Sat, 04 Aug 2007 05:36:36 GMT

some problem is there in your select statement i have made some modifications try like this

SELECT A~MBLNR A~MJAHR A~BLART A~BUDAT B~BWART B~MATNR B~ERFMG B~EBELN B~WERKS B~LGORT B~LFBNR B~SMBLN
              FROM MKPF AS A JOIN MSEG AS B ON A~MBLNR = B~MBLNR AND
                                                               A~MJAHR = B~MJAHR INTO TABLE ITAB A~BUDAT ( BETWEEN DATE1 AND DATE2 )
AND A~MJAHR = S_YEAR 
AND A~BLART IN ('WA','WE','WL')
AND B~BWART IN ( '101','531' ,'102','532' ) AND B~WERKS = S_WERKS.

Reward all helpfull answers

Regards

Pavan