topic Re: Select options excessively large in Application Development and Automation Discussions

Select options excessively large

Former Member — Wed, 17 Sep 2008 10:31:15 GMT

Dear experts,

I'm having a performance problem with this select


  SELECT matnr mtart FROM mara
                INTO CORRESPONDING FIELDS OF TABLE it_materiais
               WHERE matnr IN s_matnr
                 AND lvorm NE 'X'.

S_matnr is a select option, and when it's excessively large I get a dump.

The addition FOR ALL ENTRIES only works for internal tables...

Any idea how can I solve this issue?

Thanks in advance for any help.

Regards,

sb.

Re: Select options excessively large

Former Member — Wed, 17 Sep 2008 10:38:39 GMT

Hi,

Remove 'into corresponding fields of''.

If your it_materials containg many fields,

Instead of that declare a temporary internal table and fetch data into that after pass this to your table it_materials.

Re: Select options excessively large

Former Member — Wed, 17 Sep 2008 11:03:26 GMT

Hi Bala,

I can't remove the 'into corresponding fields of' because the internal table it_materiais doesn't have the same structure of MARA. And passing the values in s_matnr to it_materiais before select data doesn't work if there is a range.

Thanks anyway.

sb.

Re: Select options excessively large

ThomasZloch — Wed, 17 Sep 2008 11:17:04 GMT

> S_matnr is a select option, and when it's excessively large I get a dump.

What do you mean exactly, many single values or a very wide range? What is the dump title and text under "error analysis"?

Re: Select options excessively large

Former Member — Wed, 17 Sep 2008 11:35:47 GMT

Hi Thomas,

The dump title is


Runtime Errors DBIF_RSQL_INVALID_RSQL
Exception CX_SY_OPEN_SQL_DB     
Error in the module RSQL accessing the database interface.

And the "error analysis" is


An exception occurred. This exception is dealt
. The exception, which is assigned to the clas
 neither caught nor passed along using a RAISING clause, ...

Thanks for any help,

sb.

Re: Select options excessively large

Former Member — Wed, 17 Sep 2008 11:55:18 GMT

Hi Sara Bernardo,

DBIF_RSQL_INVALID_RSQL

The reason for the error is large selection data set for the field MATNR...

When it will take more time to execute the select statement and as time exceeds limit set in the systen it will through DUMP...

What Can be done to resolve the issue is ...

Make the Field MATNR mandatory...

U will be getting the dumb in case when u are not entering any matnr value and keeping it blank so it is fetching whole table entries and it's taking too mch time for it... as a result u r getting dump...

U can restrict the users to select limited MATNR...

U can add some more fields in the selection screen and can put into where clause of ur select query..

OR u can ask to BASIS if they can change the time for execution of the select Query...

Hope it will solve your problem..

Thanks & Regards

ilesh 24x7

Re: Select options excessively large

ThomasZloch — Wed, 17 Sep 2008 12:12:31 GMT

> DBIF_RSQL_INVALID_RSQL

Sounds like you're filling too many single values into S_MATNR. Depending on various circumstances (database, length of the selection field) the maximum number of single values is around 2,000 to 4,000.

You will have to divide the selection into several smaller chunks, I'm afraid, or use ranges.

Thomas

Re: Select options excessively large

Former Member — Wed, 17 Sep 2008 12:45:16 GMT

Hi Sara,

since your table is not od structure mara, better create a structure like this

Types: Begin of ty_mara,

MATNR type mara-MATNR,

MTART type mara-MTART,

end of ty_mara.

Data: it_materiais type standard table of ty_mara.

Now, remove the into corresponding fields of and use just into table ie,

SELECT matnr mtart FROM mara

INTO TABLE it_materiais

WHERE matnr IN s_matnr

AND lvorm NE 'X'.

Hopefully this is will not create a dump i guess, Check this.

Edited by: Shobana k on Sep 17, 2008 2:48 PM

Re: Select options excessively large

Former Member — Wed, 17 Sep 2008 13:10:02 GMT

Thanks for all the ideas.

I'll try some of them and give you a reply later... and points too

Regards,

sb.

Re: Select options excessively large

Former Member — Wed, 24 Sep 2008 12:40:16 GMT

Hi,

I solve the problem by creating a range.

Thanks to you all.

Regards,

sb.