1

My report has a screen with select-option ranges, if the user enters few entries the report works fine, but if the user enters a few thousand entries in the ranges the dump occurs:

dbsql_stmnt_too_large

The system says there is a problem with the SELECT query below. It is too complicated. I haven't any ideas how I solve it. Can someone help me how I fix it?

The query seems like that:

SELECT * FROM ZDBtab INTO TABLE gt_itab WHERE dbfeld1 LE gv_feld1
AND dbfeld2 IN gt_itab2
AND (  
       ( dbfeld3 NE ' ' AND dbfeld3 NE gv_feld2 )
    OR ( dbfeld4 NE ' ' AND dbfeld4 NE gv_feld2 )
    OR ( dbfeld5 NE ' ' And dbfeld5 NE gv_feld2 )
    ).

It is a Hana DB with normal ERP not S4 but HanaDB

Philipp
  • 67,764
  • 9
  • 118
  • 153
Kirve
  • 13
  • 2
  • 8

2 Answers2

2
IN gt_itab2    " <<< will have too many entries for 1 statement

The IN state is converted to a OR statement for each entry in the table. The actual statement size for 1 SQL statement is limited. (64k by default) https://maxdb.sap.com/doc/7_6/f6/069940ccd42a54e10000000a1550b0/content.htm

You can try use the for all entries option, or some other access strategy to avoid the large IN table option.

EDIT: After info about actual DB was provided. So HANA DB here. HANA DB SQL Docu It is a huge 2gb statement size on HANA. So perhaps some other Memory allocation limit was exhausted building the SQL statement. At any rate if you have a very large number of entries in gt_itab2 , then that is very likely the issue.

phil soady
  • 11,043
  • 5
  • 50
  • 95
  • 1
    The limit depends on the database system and version, the OP didn't say what it is yet. Your reference concerns only database SAP MaxDB version 7.6. – Sandra Rossi Dec 02 '21 at 06:40
  • Yeah sorry. It is a hana db with normal erp not s4hana. But the database is hana – Kirve Dec 02 '21 at 07:11
  • The is a similar restriction on all the ERP DBs. The statement still needs to be formed on HANA too. So what is the max statement size on HANA? – phil soady Dec 02 '21 at 08:07
  • Where I can check it? Yeah for testing we input about 60thousand document number in selection screen. And then happens this dump. At a lower soze of selection the code works – Kirve Dec 02 '21 at 08:49
  • the 64 kB mentioned in the first link was [increased to 1 MB](https://launchpad.support.sap.com/#/notes/1002491) more than a decase ago. The link is for Oracle, but it happened in other DB too. – András Dec 06 '21 at 10:42
0

Check if that many entries are even necessary

In my experience, users do not need "a few thousand entries". Either there are many duplicates, or they have to use the result of one report as the input of another. This usually means it should have been one report instead of two to begin with.

If you are stuck with the current setup, you can still fix it from thechnical side.

Check for duplicates, then cut gt_itab2 up into smaller parts

  1. Check for duplicates, then remove them

    a. SORT + DELETE ADJACENT DUPLICATES is the easiest

    b. COLLECT is the fastest

  2. If gt_itab2 only contains I and EQ, you can use FOR ALL ENTRIES

  3. If not, you can cut gt_itab2 into slices of 1000 (or whatever your DB can handle) and SELECT in a WHILE until you processed all of gt_itab2

András
  • 1,326
  • 4
  • 16
  • 26