cancel
Showing results for 
Search instead for 
Did you mean: 

RANGES....size limit

Former Member
0 Kudos

Hello,

I have created a RANGE for some 3500+ records and then used the same in SELECT statement. I am getting DUMP in select statement. Is there any restiction on the size of RANGES ..table?

Please suggest .. Thank you !!

Regards,

Vishal Tyagi

Accepted Solutions (0)

Answers (11)

Answers (11)

Former Member

Hi VVishal,

It is not a problem of the ranges-linmitation; it is a problem of the SQL-Parser.

If you use ranges within a select statement, the SQL-Parser cretaes the SQL-Statement and it is limited by a certain value auf Bytes (1024?). (All the ranges-entries will be translated into a SQL-string!).

Better way is to create an internal table and use the statement

select *

from TAB

for all entries in ITAB

where TAB-KEY = ITAB-KEY.

This works fine.

BR Michael

Former Member
0 Kudos

Hello SAP experts,

Is it a good idea to use 'for all entries in <ITAB>' for select with join ?

Its really great to have your invalueable comments on this. Well there is no dump if I use for all entries in <ITAB>.

But, Is it a good idea to use 'for all entries in <ITAB>' for select with join ? The select statement I am using here is a JOIN on 2 tables.

System is giving a dump SAPSQL_STMNT_TOO_LARGE, when I use RANGES, with select statement. The in DUMP says 'There is no help text for this dump Either the text was inadvertently deleted or the release of the kernel differs from the release of the database ' ... ??? Why ???

Thank you !!

Regards,

Vishal Tyagi

Message was edited by: Vishal Tyagi

Former Member
0 Kudos

Hi Vishal,

You might very well use FOR ALL ENTRIES but one caution that you need to maintain here is, that you select all the fields that uniquely identify a row, because otherwise when SAP does a union of the resultant sents some entries maybe lost.

As a simple approach one might try to select all the key fields whenever using FOR ALL ENTRIES.

Pavan

Former Member
0 Kudos

Hello Vishar,

Of course. You can use <FOR ALL ENTRIES> in any case,

where the amount of comparing fileds became to much.

But it is good practice to select only the fields,

You really need.

BR

Michael

Former Member
0 Kudos

> Hello,

> I have created a RANGE for some 3500+ records and

> then used the same in SELECT statement. I am getting

> DUMP in select statement. Is there any restiction on

> the size of RANGES ..table?

>

> Please suggest .. Thank you !!

>

> Regards,

> Vishal Tyagi

Hello Vishal tyagi sir ,

Good to see you in this forum. yup you do have a restriction in select options. when you fire a select statment in sap to fetch data , all the ranges are converted into single and clauses .

pasting a original sap quote for the problem. hope this helps.

"When the SQL statement is generated at runtime, you should check the following restrictions and divide or reduce the statement if necessary. The static length (in the ABAP editor) of any ABAP command must not exceed 28672 characters.In Open SQL commands with dynamic subclauses

(WHERE, GROUP BY, ORDER BY, and so on), the subclauses cannot contain more than 28672 characters. "

Former Member
0 Kudos

Hello all

I had the same problem, when I changed the range to select-option and no-display, it worked but it 's taking way too much time, the table has 1000 records. the values come from a cost center group, and if you use the all entries statement then if you have a range in the table you might loose data.

is there a function module to capture all values for a given cost center without any range in the table, this way the all entries staetment would be useful.

anyone has an idea.

Former Member
0 Kudos

Ulrich, I've found changing to the SELECT-OPTION makes a difference. Since a range is normally internal, I suggested the use of No-Display with the SELECT-OPTION. SAP appears to handle the access better when working with large selection lists.

ulrich_koch
Explorer
0 Kudos

Hello Vishal,

please have a look at SAP note 635318. It tries to explain how an IN condition is transformed into a part of a WHERE clause and it explains which size limitations apply for Open SQL statements. Whether you can use FOR ALL ENTRIES instead depends on the kind of selection you do. If all rows in your range table got the same sign and option (I, EQ e.g.) then you can replace the IN condition by FOR ALL ENTRIES. But be aware of the facts that:

- FOR ALL ENTRIES is always implicitly a SELECT DISTINCT and that

- an empty FOR ALL ENTRIES table results in no WHERE clause at all, thus selecting the whole database table.

Using FOR ALL ENTRIES together with JOINs is absolutely ok.

Best regards

Ulrich

Former Member
0 Kudos

Range tables although formatted like a select option are not handled in the same way. If you want to use the IN operand instead of the FOR ALL ENTRIES version of the select statement. Change the range to a select option that is "no display". SAP will manage the size of the selection passed to the database and the "too large" problem will be handled.

ulrich_koch
Explorer
0 Kudos

Sorry, the addition "no display" won't change a thing. You get an ordinary range table with the same limitations when used inside an Open SQL statement.

Former Member
0 Kudos

I've found changing to the SELECT-OPTION makes a difference. Since a range is normally internal, I suggested the use of No-Display with the SELECT-OPTION. SAP appears to handle the access better when working with large selection lists.

Former Member
0 Kudos

I've found changing to the SELECT-OPTION makes a difference. Since a range is normally internal, I suggested the use of No-Display with the SELECT-OPTION. SAP appears to handle the access better when working with large selection lists.

Former Member
0 Kudos

I've found changing to the SELECT-OPTION makes a difference. Since a range is normally internal, I suggested the use of No-Display with the SELECT-OPTION. SAP appears to handle the access better when working with large selection lists.

ssimsekler
Active Contributor
0 Kudos

Hi!

Whatsoever, if the problem is due to the restriction imposed by the underlying DB system, the solution is to use some simpler "SELECT" statements which will not be problematic when parsed by RDBMS and sent to the DB system. For the problem with "RANGES", SAP recommends to use "FOR ALL ENTRIES in <itab>" addition.

If you have joins and the result is a dump, then this means that your DB system cannot handle your OpenSQL interpretation and I think the way is to change the SQL.

*--Serdar

ssimsekler
Active Contributor
0 Kudos

Hi

Yes, there is a restriction. But as it is told in the dump message, it is an exception imposed by the underlying database system. SAP RDBMS arranges a new SQL statement while reaching to the database system. There, conditions having many parameters (e.g. defined with an "IN" statement) causes a bunch of ORs which yields erroneous SQL statements for DB. The limit depends on the database system. ~3000 record may be an exceeding number for MS SQL Server whereas it is not for an Oracle system. Some hints for solution are also given in the dump message.

*--Serdar

Former Member
0 Kudos

Hallo Vishal,

Andreas is right, the SELECT is too big. These restrictions have nothing to do with the RANGES table but with your database so it's hard to say exactly how big the table can be without this problem arising.

Even if the RANGES table wasn't too big I suspect that the SELECT would be rather inperformant since it's interpreted by the DB as WHERE statements rather than a single IN. Perhaps it would be better to specifically read the key fields from the DB-table into an internal table and then to throw out the superfluous entries thus leaving just the key fields. Then you could read the DB-table quickly with a proper index.

Regards Gerard.

andreas_mann3
Active Contributor
0 Kudos

Hi Vishal,

I think , you've got RSQL error 13,

because a select statemnet exceeds maximum size.

( there's an restriction of ~ 3000 )

-> look at ST11 for more info

Grx

Andreas

Former Member
0 Kudos

Hi Vishal,

Could you please let us know what is the message you get on Short Dump ? The <i>long text</i> and <i>error analysis</i> might help, too.

Regards,

Anand Mandalika.