topic Re: Using RANGE table in SELECT statement in Application Development and Automation Discussions

Using RANGE table in SELECT statement

former_member3635 — Mon, 18 Jan 2021 17:56:07 GMT

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.

Re: Using RANGE table in SELECT statement

Patrick_vN — Mon, 18 Jan 2021 18:33:38 GMT

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.

Re: Using RANGE table in SELECT statement

Sandra_Rossi — Tue, 19 Jan 2021 07:56:09 GMT

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

Re: Using RANGE table in SELECT statement

RaymondGiuseppi — Tue, 19 Jan 2021 09:02:49 GMT

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)

Re: Using RANGE table in SELECT statement

former_member3635 — Tue, 19 Jan 2021 09:59:59 GMT

Thank you Patrick,

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