topic Re: Select contains too many Input variables!!! in Application Development and Automation Discussions

Select contains too many Input variables!!!

Former Member — Tue, 29 Sep 2009 12:25:05 GMT

Hi,

I haev select statement below for which i will pass Input as more than 5000 entries in single values of Select options. When i pass 3000 entries in the select -options of S_BP than iam facign a dump with the following errors.

1. The maximum size of an SQL statement has been exceeded.

2. The statement contains too many input variables.

3. The space needed for the input data exceeds the available memory.

select-options: s_bp FOR but000-partner. "Business Partner

Fetch BP data

IF NOT s_bp[] IS INITIAL.

SELECT partner "Business Partner Number

name_org1 "Name 1 of organization

crusr "User

INTO TABLE i_but000

FROM but000

WHERE partner IN s_bp.

ENDIF.

Regards,

Deepthi.

Re: Select contains too many Input variables!!!

Former Member — Tue, 29 Sep 2009 12:35:06 GMT

Hi ,

a) Discuss with basis and try to take some help from him for memory related problem.

b) You can upload input variables in an internal table and use the internal table in your select statment using for all entries statment.

Re: Select contains too many Input variables!!!

Former Member — Tue, 29 Sep 2009 12:38:38 GMT

consult BASIS guys to increase the memory size

Re: Select contains too many Input variables!!!

Former Member — Tue, 29 Sep 2009 12:42:20 GMT

Hi, the number of entries in the select options is limited. Maybe you can use a inputfile or excel sheet. Read it into a internal table and use the internal table in a SELECT statement with FOR ALL ENTRIES in. Success

Re: Select contains too many Input variables!!!

SuhaSaha — Tue, 29 Sep 2009 12:46:38 GMT

Hello Deepthi,

Please look into the OSS Notes 635318.

There is a limit to the size of the SQL statement in the DB. For non-unicode it is 28672 characters & for unicode it is 14336 characters.

BR,

Suhas

Re: Select contains too many Input variables!!!

rainer_hbenthal — Tue, 29 Sep 2009 12:49:43 GMT

there is no way to solve that, increasing memory size is nonsense.

select options are SAP internal stuff, no SQL dialect can understand this. So the OPEN SQL statement has to be translated to the one the underlying DBMS can understand and often enough, SQL staements are limited in size. In the DB2 Cockpit you can see how such an open sql statement is translated, the select option is mostly translated into a queue of or statements. resulting in a biiiiiiiiiiiiiiiiiiiiiiiiiiig SQL statement which cause a dump, the DBMS cant handle it.

You need to packetize your select option into a range table of let me say up tpo 50 values handled by the sql statement und join the result.

If the report runs in background you can solve it in an old fashioned way: just export the whole table into a flat file, sort the file via external commands, and then filter the flat file with awk and/or grep. Finally read in the flat file,

Re: Select contains too many Input variables!!!

Former Member — Tue, 29 Sep 2009 12:50:59 GMT

The size of memory for where condition is limited, if you exceed it a dump occurs.

A tipical soultion for this problem is to split the selection, something like this:

select-options: s_bp FOR but000-partner. "Business Partner

DATA: HIT_MAX TYPE I VALUE 1000.
DATA: START_INDEX TYPE I,
           FROM_INDEX    TYPE I,
           SEL_INDEX      TYPE I.

RANGES R_BP FOR BUT00-PARTNER.


DESCRIBE TABLE S_BP LINES SEL_INDEX.
IF SEL_INDEX <= HIT_MAX
   SELECT name_org1 crusr INTO TABLE i_but000
      FROM but000 WHERE partner IN s_bp.
ELSE.
  FROM_INDEX = 1.
  TO_INDEX      = HIT_MAX.
  DO.
    APPEND LINES OF S_RBP FROM START_INDEX TO FROM_INDEX TO R_BP.
    SELECT name_org1 crusr APPENDING TABLE i_but000
        FROM but000 WHERE partner IN R_BP.
    REFRESH R_BP.
    FROM_INDEX = TO_INDEX + 1.
    IF FROM_INDEX > SEL_INDEX.
       EXIT.
    ENDIF.
    TO_INDEX = TO_INDEX + HIT_MAX.
    IF TO_INDEX > SEL_INDEX.
       TO_INDEX = SEL_INDEX.
   ENDIF.
  ENDDO.
ENDIF.

Max

Re: Select contains too many Input variables!!!

Former Member — Tue, 29 Sep 2009 12:59:44 GMT

Hi Deepthi,

Yes you could not use too many rows in your Select-Options. Do you really need to do that?

Anyway, you can avoid your dump, if you really want to keep your code, by using a FOR ALL ENTRIES on your Select-Option like this :


SELECT partner name_org1 crusr
  INTO TABLE i_but000
  FROM but000
  FOR ALL ENTRIES IN s_bp
    WHERE partner = s_bp-low.

Best regards,

Samuel

Re: Select contains too many Input variables!!!

rainer_hbenthal — Tue, 29 Sep 2009 13:05:57 GMT


> Hi Deepthi,
> 
> Yes you could not use too many rows in your Select-Options. Do you really need to do that? 
> 
> Anyway, you can avoid your dump, if you really want to keep your code, by using a FOR ALL ENTRIES on your Select-Option like this :
> Samuel

No he cant For all entries is an open sql clause which needs to by translated to native sql. The resulting SQL will be too big, too.

Re: Select contains too many Input variables!!!

ThomasZloch — Tue, 29 Sep 2009 13:15:48 GMT

> The resulting SQL will be too big, too.

Not correct, the DBI will split it into blocks before handing it to the DB, subject to parameter "rsdb/max_blocking_factor" in RZ11, check out the documentation there.

Thomas

Re: Select contains too many Input variables!!!

Former Member — Tue, 29 Sep 2009 13:23:43 GMT

Hi Samuel,

when i used ur select query than im got only first record.when i have 2865 entries as input to S_BP but in the output there is only one entry ie., first entry.

Cheers,

deepthi.

Re: Select contains too many Input variables!!!

rainer_hbenthal — Tue, 29 Sep 2009 13:34:18 GMT

You're right, if and only if the open sql statement will be translated in such a way. It will be done this way sometimes, but not always.

Re: Select contains too many Input variables!!!

Former Member — Tue, 29 Sep 2009 13:43:59 GMT

Just as I said before you should split your selection, that's a typical solution (used in standard program too)

Max

Re: Select contains too many Input variables!!!

Former Member — Tue, 29 Sep 2009 13:53:59 GMT

Hi Max,

I am tryign to use the same logic what u said above but im facing a dump at APPEND LINES OF s_bp FROM v_start_index TO v_from_index TO r_bp. I haev trie to add v_start_index = sy-index before append statement. the problem here is v_start_index does not have any value ie., it is zero.

my code is as below:

IF NOT s_bp-low IS INITIAL AND s_bp-high IS INITIAL.

DATA: v_hit_max TYPE i VALUE '1500',

v_start_index TYPE i,

v_from_index TYPE i,

v_to_index TYPE i,

v_sel_index TYPE i.

RANGES: r_bp FOR but000-partner.

DESCRIBE TABLE s_bp LINES v_sel_index.

IF v_sel_index <= v_hit_max.

If the S_BP got 1500 records as Input,
directly fetch the entries from BUT000

SELECT partner

name_org1

crusr

INTO TABLE i_but000

FROM but000

WHERE partner IN s_bp.

ELSE.

If S_BP got more than 1500 records as Input,
do below logic

v_from_index = 1.

v_to_index = v_hit_max.

DO.

v_start_index = sy-index.

APPEND LINES OF s_bp FROM v_start_index TO v_from_index TO r_bp.

SELECT partner

name_org1

crusr

INTO TABLE i_but000

FROM but000

WHERE partner IN r_bp.

REFRESH r_bp.

v_from_index = v_to_index + 1.

if Start index no is greater than total no of entries in S_BP, than EXIT

IF v_from_index > v_sel_index.

EXIT.

ENDIF.

v_to_index = v_to_index + v_hit_max.

if End index no is greater than total no of entries in S_BP,
than pass the maximum index to End index

IF v_to_index > v_sel_index.

v_to_index = v_sel_index.

ENDIF.

ENDDO.

ENDIF.

endif.

Regards,

Deepthi.

Re: Select contains too many Input variables!!!

Former Member — Tue, 29 Sep 2009 14:03:41 GMT

U're usign the variable v_start_index instaed of V_FROM_INDEX:

 v_from_index = 1.
  v_to_index   = v_hit_max.

  DO.
* v_start_index = sy-index.
* APPEND LINES OF s_bp FROM v_start_index TO v_from_index TO r_bp. "<-------------------
   APPEND LINES OF s_bp FROM v_from_index TO v_to_index TO r_bp.
   SELECT partner name_org1 crusr
      INTO TABLE i_but000
        FROM but000
          WHERE partner IN r_bp.

    REFRESH r_bp.
    v_from_index = v_to_index + 1.
* if Start index no is greater than total no of entries in S_BP, than EXIT
    IF v_from_index > v_sel_index.
      EXIT.
    ENDIF.
    v_to_index = v_to_index + v_hit_max.
* if End index no is greater than total no of entries in S_BP,
* than pass the maximum index to End index
    IF v_to_index > v_sel_index.
      v_to_index = v_sel_index.
    ENDIF.
  ENDDO.

Max

Re: Select contains too many Input variables!!!

Former Member — Tue, 29 Sep 2009 14:26:00 GMT

As everyone here said, there is a limit to how large a SQL statement can be and that is a database limitation. If you have single values in your select option, then the resulting SQL statement will look like WHERE field1 = value1 OR field1 = value2 ...... OR field1 = value5000. That is why you are getting the error.

Your options inlcude changing your select statement to FOR ALL ENTRIES. But you have remove the ranges option from your select option.

SELECT partner name_org1 crusr INTO TABLE i_but000
                                       FROM but000
                         FOR ALL ENTRIES IN s_bp
                                      WHERE partner = s_bp-low.

You can also try to do what Max said by limiting your entries in S_BP. To do that you can simply do this.

DATA: v_count TYPE i.
DATA: r_bp TYPE RANGE OF but000-partner.

LOOP AT s_bp.
  APPEND s_bp TO r_bp.
  v_count = v_count + 1.
  IF v_count = 1000.
    SELECT partner name_org1
                       crusr APPENDING TABLE i_but000
                                        FROM but000
                                       WHERE partner IN r_bp.
    REFRESH r_bp.
    CLEAR v_count.
  ENDIF.
ENDLOOP.

Re: Select contains too many Input variables!!!

Former Member — Tue, 29 Sep 2009 14:39:24 GMT

Deepthi,

Are you sure you have use the line with the FOR ALL ENTRIES? It is necessary to use the range as a table and not only as a working area...

Best regards,

Samuel

Re: Select contains too many Input variables!!!

Former Member — Tue, 29 Sep 2009 15:13:36 GMT

Hi Max, Thanks a Lot

It is working Super...

Regards,

Deepthi.