Using RANGE table in SELECT statement

[former_member3635]

Hello,

I am getting a short dump -> Err.tmpo.ejec. DBSQL_STMNT_TOO_LARGE Excep. CX_SY_OPEN_SQL_DB

in the following code:

SELECT * FROM pcl2
INTO TABLE lt_pcl2
WHERE relid EQ 'B2'
AND srtfd IN rl_srtfd <------cause of the error
AND srtf2 EQ '00'.

The error occurs because the range table rl_srtfd has 32664 rows, which are too many to be processed in the SELECT statement.

I'm not sure what is the limit because I've tried the program with only 10000 rows in rl_srtfd and it didn't give a dump.

How should I proceed to solve this? How do I know the limit of the size of rl_srtfd?

Thank you all in advance.

[Patrick_vN]

If your range contains so much values, the generated SQL statement is too long (snote 2317211).

Easiest solution would be splitting your range in several ranges and run a SQL statement for each of those ranges (containing 1000-1500 values or so).

Depending on the how, where, why and when you could also try and find another solution and don't use ranges at all.

[former_member3635]

Thank you Patrick,

Another, less efficient, option would be to remove that condition and afterwards delete all undesired entries

[Sandra_Rossi]

If it works for 10.000 then keep that number and you should never have the issue again.

[RaymondGiuseppi]

The maximal size of a SQL statement depends on your database. Perform some test if database manager cannot answer.

• If your range contains only include records criteria (sign = 'I') you can split the range in smaller ones and load data in packet. (In this case removing duplicate records may be required)
• If your range contains only a list of values (sign = 'I', option = 'EQ') you can use a simple FOR ALL ENTRIES option (Abap SQL will remove duplicates)